FY2022 Analysis - Methodology for Fiscal Futures
Alea Wilbur
December 6 2022
library(tidyverse)
library(haven)
library(formatR)
library(lubridate)
library(smooth)
library(forecast)
library(scales)
library(kableExtra)
library(ggplot2)
library(readxl)
library(tidyverse)
library(data.table)
library(quantmod)
library(geofacet)
library(janitor)
knitr::opts_chunk$set(echo = TRUE, warning = FALSE, message = FALSE)#, cache = TRUE)Data Creation and Cleaning
Do the FOIA request. In a week or so, they send the expenditure and revenue data as excel files.
Checks whether there are any new agencies, re-used funds etc. Create a list of funds, agencies, fund names, etc. for the new year and compare it to the immediate prior year to identify new funds.
Update the funds_ab_in file which shows the use of funds. Use criteria to determine if the new funds should be in or out of the all-funds frame.
Change the variable names to be consistent with other files such as AGENCYNAME –> agency_name
Once variable names are shared over all years of data, combine past years with newest year. All revenue files are in a
revenuefolder that I reference when I set the working directory. When adding new fiscal years, put the the newest year of data for revenue and expenditures in their respective folders.
Normally, when your receive the new fiscal year files from the Comptrollers office, you will need to change the variable names so that they are consistent with past years. This is an example of reading in the new file and changing the variable names. They seem to change almost every year in the file received from the FOIA so if the code breaks here, check to make sure that the columns you are trying to rename exist and are spelled correctly! Once variables are the same, you will want to save the file as a csv file in its Revenue/Expenditure file and bind all past years and the current year together in one dataframe.
#Example code below: Read in excel file and rename columns so that it plays well with the other years' files.
revenue_fy22 <- read_xlsx("Fis_Fut_Rev_2022_Final.xlsx") %>%
rename(fy = 'FY',
fund = 'FUND',
fund_name = 'FUND NAME',
agency = 'AGENCY',
agency_name = 'AGENCY NAME',
source = 'REVENUE SOURCE',
source_name = 'REV SRC NAME',
receipts = 'AMOUNT'
)
exp_fy22 <- read_xlsx("Fis_Fut_Exp_2022_Final.xlsx") %>%
rename(fy = 'FY',
fund = 'FUND',
fund_name = 'FUND NAME',
agency = 'AGENCY',
agency_name = 'AGENCY NAME',
appr_org = 'ORGANIZATION',
org_name = 'ORGANIZATION NAME',
obj_seq_type = 'APPROPRIATION',
wh_approp_name = 'APPROPRIATION NAME',
# exp_net_xfer = 'NET OF TRANS AMOUNT',
expenditure = 'EXPENDED'
)
# %>%
# # these come from ioc_source file after merging
# mutate(data_source = "exp IOC Aug 2022",
# object = ,
# seq = ,
# type = ,
# fund_cat = FIND_COLUMN, #create fund_cat column
# fund_cat_name = FIND_NAME) # create fund_cat_name columnThe code chunk below takes the .dta files for all fiscal years before FY 2022 and binds them together. Variable names were manually changed by past researchers so that they were consistent across years.
For FY 2023 and after, .dta files can be avoided entirely and .csv or excel files will be used. All files before this year had been saved and passed on as .dta files for Stata code before the transition to R in Fall 2022. For years after fy22: add line of code to bind csv files after binding the dta files together. Variable names must identical to merge files together.
Code below reads in the csv files created in chunks above (allrevfiles.csv and allrexpfiles.csv). These files contain all years of data combined into one file BEFORE any recoding is done. Do not use this file for summing categories because it is just an inbetween step before recoding revenue and expenditure categories.
# combined in past chunks called create-rev-csv and create-exp-csv
allrevfiles22 <- read_csv("allrevfiles22.csv") #combined but not recoded
allexpfiles22 <- read_csv("allexpfiles22.csv") #combined but not recodedInspecting new FY22 data files
Data files for closed years have been obtained from IOC. The numbers of funds, agencies, organizations, and revenue sources below were found by using pivot tables in the codebook files in the FY2022 Box folder. This could also be done using R and grouping new files by fund, agency, source number, source names, etc..
Revenue File:
- 684 Fund Numbers
- 80 Agencies
- 1184 Revenue source numbers - 1156 revenue source names
Expenditure File:
- 708 Fund Numbers
- 107 Agencies
- 98 Organization Numbers
- 313 Organization names
Finding new agencies and funds
General steps:
1. Identify new and reused funds for newest fiscal year. 2. Recode funds
to take into account different fund numbers/names over the years. See Recoding New and Reused Funds
for code chunk that does this. 3. Update fund_ab_in_2022.xlsx with any
changes from previous fiscal year.
New Agencies, Funds, and Organizations from Expenditure files:
- Using the code below, I found 2 agencies, multiple funds, and a
couple organizations that had not been specifically mentioned in the
funds_ab_in file - AWM, FY2022.
- Note: One new org name and org number combo has an expenditure of $600 million for pensions payment. Investigate later.
allrevfiles22 <- read_csv("allrevfiles22.csv") #combined but not recoded
allexpfiles22 <- read_csv("allexpfiles22.csv") #combined but not recoded
#### From Expenditure Data #####
# agencies referenced in any year before 2020:
agencies_past <- allexpfiles22 %>% filter(fy < 2022) %>% mutate(agency == as.character(agency)) %>% group_by(agency, agency_name) %>% unique() %>% summarize(expenditure = sum(expenditure, na.rm = TRUE)) %>% drop_na() %>% arrange(agency)
# agencies_past # 146 agencies ever
# agencies in 2022 data:
agencies22 <- allexpfiles22 %>% filter(fy == 2022) %>% mutate(agency == as.character(agency)) %>% group_by(agency, agency_name) %>% summarize(expenditure = sum(expenditure, na.rm = TRUE))
# agencies22 # 107 agencies this year
# 280 and 533 are new agency codes:
anti_join(agencies22, agencies_past, by = c("agency", "agency_name")) %>% arrange(agency)funds_past <- allexpfiles22 %>% filter(fy < 2022) %>% mutate(fund == as.character(fund)) %>% group_by(fund, fund_name) %>% summarize(count = n(), Expenditure = sum(expenditure, na.rm = TRUE)) %>% drop_na()
funds22 <- allexpfiles22 %>% filter(fy == 2022) %>% mutate(fund == as.character(fund)) %>% group_by(fund, fund_name) %>% summarize(count = n(), Expenditure = sum(expenditure, na.rm = TRUE)) %>% unique()
# 15 funds were in FY22 data that were not in past data:
anti_join(funds22, funds_past, by = c("fund", "fund_name")) %>% arrange(fund)# orgs_pastin the past = 916 org groups ever
orgs_past <- allexpfiles22 %>% filter(fy < 2022) %>% mutate(appr_org == as.character(appr_org)) %>% group_by(appr_org, org_name) %>% unique() %>% summarize(Expenditure = sum(expenditure, na.rm = TRUE)) %>% drop_na()
# orgs_past # 916 org groups ever
orgs22 <- allexpfiles22 %>% filter(fy == 2022) %>% mutate(appr_org = as.character(appr_org)) %>% group_by(appr_org, org_name) %>% summarize(Expenditure = sum(expenditure, na.rm = TRUE))
# orgs22 # 396 org groups this year
# 4 org number and org name combos are new for FY2022:
anti_join(orgs22, orgs_past, by = c("appr_org", "org_name")) %>% arrange(appr_org)New Revenue Funds, Sources, and New Agencies:
#### From Revenue Data ####
# agencies_past # 108 agencies ever
agencies_past <- allrevfiles22 %>% filter(fy < 2022) %>% mutate(agency == as.character(agency)) %>% group_by(agency, agency_name) %>% unique() %>% summarize(Receipts = sum(receipts, na.rm = TRUE)) %>% drop_na()
# agencies22 # 80 agencies this year
agencies22 <- allrevfiles22 %>% filter(fy == 2022) %>% mutate(agency == as.character(agency)) %>% group_by(agency, agency_name) %>% summarize(Receipts = sum(receipts, na.rm = TRUE))
# 0 new agencies in revenue data this year
anti_join(agencies22, agencies_past, by = c("agency", "agency_name")) %>% arrange(agency)funds_past <- allrevfiles22 %>% filter(fy < 2022) %>% mutate(fund == as.character(fund)) %>% group_by(fund, fund_name) %>% summarize(count = n(), Receipts = sum(receipts, na.rm = TRUE)) %>% drop_na()
funds22 <- allrevfiles22 %>% filter(fy == 2022) %>% mutate(fund == as.character(fund)) %>% group_by(fund, fund_name) %>% summarize(count = n(), Receipts = sum(receipts, na.rm = TRUE)) %>% unique() %>% drop_na()
# 19 revenue funds were in FY22 revenue data that were not in past data
# some could be small fund name changes
anti_join(funds22, funds_past, by = c("fund", "fund_name")) %>% arrange(fund)sources_past <- allrevfiles22 %>% filter(fy < 2022) %>% mutate(source == as.character(source)) %>% group_by(source, source_name) %>% summarize(count = n(), Receipts = sum(receipts, na.rm = TRUE)) %>% drop_na()
sources22 <- allrevfiles22 %>% filter(fy == 2022) %>% mutate(source == as.character(source)) %>% group_by(source, source_name) %>% summarize(count = n(), Receipts = sum(receipts, na.rm = TRUE)) %>% unique()
# 20 revenue sources were in FY22 data that were not in past data
# some could be small source name changes:
anti_join(sources22, sources_past, by = c("source", "source_name")) %>% arrange(source)Sources 2737 through 2756 were not found in the IOC_source file so I
added them to ioc_source_updated22_AWM.xlsx. They do NOT
have a rev_type until the Fiscal Futures researchers discuss which
revenue type the sources fall under and if they should be included in
the analysis in general.
Recoding New and Reused Funds
New funds will need to be manually added to the funds_ab_in excel file and determined if they should or should not be included in Fiscal Future calculations. Copy info from word file here on how to add funds to excel file.
For funds that were reused once, a 9 replaces the 0 as the first
digit. If reused twice, then the first two values are 10.
- Ex. 0350 –> 9350 because its use changed.
- Ex. 0367 becomes 10367 because its use has changed twice now. There
was fund 0367 originally, then its use changed and it was recoded as
9367, and now it changed again so it is a 10367.
- Excel file also has alternative ways to name funds (e.g. 0397-A and
0397-B) and variables for the year that the fund stopped being used.
These have not been updated consistently over the years but it is useful
information when trying to find any coding mistakes from the past.
New or reused funds revenue file recoding:
# if first character is a 0, replace with a 9 if its purpose has changed
rev_1998_2022 <- allrevfiles22 %>%
mutate(fund = ifelse(fy < 2002 & fund %in% c("0730", "0241", "0350", "0367", "0381", "0382", "0526", "0603", "0734", "0913", "0379"), str_replace(fund, "0","9"), fund)) %>%
mutate(fund = ifelse (fy < 2008 & fund %in% c("0027", "0033", "0037", "0058", "0062", "0066", "0075", "0083", "0116", "0119", "0120", "0122", "0148", "0149", "0157", "0158", "0166", "0194", "0201", "0209", "0211", "0217", "0223", "0231", "0234", "0253", "0320", "0503", "0505", "0512", "0516", "0531", "0532", "0533", "0547", "0563", "0579", "0591", "0606", "0616", "0624", "0659", "0662", "0665", "0676", "0710",
"0068", "0076", "0115", "0119", "0168", "0182", "0199", "0241", "0307", "0506", "0509", "0513"), str_replace(fund, "0","9"), fund)) %>%
mutate(fund = ifelse(fy < 2016 & fund %in% c("0263", "0399", "0409"), str_replace(fund, "0","9"), fund)) %>%
mutate(fund = ifelse(fy < 2017 & fund == "0364", str_replace(fund, "0","9"), fund)) %>%
mutate(fund = ifelse(fy < 2018 & fund %in% c("0818", "0767", "0671", "0593", "0578"), str_replace(fund, "0","9"), fund)) %>%
mutate(fund = ifelse(fy>1999 & fy < 2018 & fund == "0231", "10231", fund) ) %>%
mutate(fund = ifelse(fy < 2019 & fund %in% c("0161", "0489", "0500", "0612", "0893", "0766"), str_replace(fund, "0","9"), fund)) %>%
mutate(fund = ifelse(fy < 2020 & fund %in% c("0254", "0304", "0324", "0610", "0887", "0908", "0939", "0968"), str_replace(fund, "0","9"), fund)) %>%
mutate(fund = ifelse(fy < 2021 & fund %in% c("0255", "0325", "0348", "0967", "0972"), str_replace(fund, "0","9"), fund) ) %>%
#2022 changes
mutate(fund = ifelse(fy < 2022 & fund %in% c("0110","0165","0351", "0392", "0393", "0422", "0544", "0628", "0634", "0656", "0672", "0683", "0723", "0742", "0743"), str_replace(fund, "0","9"), as.character(fund))) %>% # replaces first 0 it finds with a 9
mutate(fund = ifelse(fy < 2022 & fund == "0367", "10367", as.character(fund)) # fund reused for 3rd time
)Expenditure recoding:
# if first character is a 0, replace with a 9
exp_1998_2022 <- allexpfiles22 %>%
mutate(fund = ifelse(fy < 2002 & fund %in% c("0730", "0241", "0350", "0367", "0381", "0382", "0526", "0603", "0734", "0913", "0379"), str_replace(fund, "0","9"), fund)) %>%
mutate(fund = ifelse(fy < 2008 & fund %in% c("0027", "0033", "0037", "0058", "0062", "0066", "0075", "0083", "0116", "0119", "0120", "0122", "0148", "0149", "0157", "0158", "0166", "0194", "0201", "0209", "0211", "0217", "0223", "0231", "0234", "0253", "0320", "0503", "0505", "0512", "0516", "0531", "0532", "0533", "0547", "0563", "0579", "0591", "0606", "0616", "0624", "0659", "0662", "0665", "0676", "0710",
"0068", "0076", "0115", "0119", "0168", "0182", "0199", "0241", "0307", "0506", "0509", "0513"), str_replace(fund, "0","9"), fund)) %>%
mutate(fund = ifelse(fy < 2016 & fund %in% c("0263", "0399", "0409"), str_replace(fund, "0","9"), fund)) %>%
mutate(fund = ifelse(fy < 2017 & fund == "0364", str_replace(fund, "0","9"), fund)) %>%
mutate(fund = ifelse(fy < 2018 & fund %in% c("0818", "0767", "0671", "0593", "0578"), str_replace(fund, "0","9"), fund)) %>%
mutate(fund = ifelse(fy>1999 & fy < 2018 & fund == "0231", "10231", fund) ) %>%
mutate(fund = ifelse(fy < 2019 & fund %in% c("0161", "0489", "0500", "0612", "0893", "0766"), str_replace(fund, "0","9"), fund)) %>%
mutate(fund = ifelse(fy < 2020 & fund %in% c("0254", "0304", "0324", "0610", "0887", "0908", "0939", "0968"), str_replace(fund, "0","9"), fund)) %>%
mutate(fund = ifelse(fy < 2021 & fund %in% c("0255", "0325", "0348", "0967", "0972"), str_replace(fund, "0","9"), fund)) %>%
#2022 changes
mutate(fund = ifelse(fy < 2022 & fund %in% c("0110","0165","0351", "0392", "0393", "0422", "0544", "0628", "0634", "0656", "0672", "0683","0723", "0742", "0743"), str_replace(fund, "0","9"), as.character(fund))) %>% # replaces first 0 it finds with a 9
mutate(fund = ifelse(fy < 2022 & fund == "0367", "10367", as.character(fund)) # fund reused for 3rd time
)The funds_ab_in.xlsx file contains all funds that have
existed since 1998, if they still exist, indicates if fund numbers have
been reused for varying purposes, and is updated yearly with new fund
numbers used by the IOC.
funds_ab_in_2022 = readxl::read_excel("C:/Users/aleaw/OneDrive/Documents/PhD Fall 2021 - Spring 2022/Merriman RA/Fiscal Futures FY2022/Replication-Files/funds_ab_in_2022.xlsx")
exp_temp <- exp_1998_2022 %>%
arrange(fund, fy) %>%
filter(expenditure != 0) %>% # keeps everything that is not zero
# join funds_ab_in_2021 to exp_temp
left_join(funds_ab_in_2022, by = "fund") # matches most recent fund number - the initial combined years of data are saved as dataframes named
exp_1998_2022andrev_1998_2022. These are then saved as exp_temp and rev_temp while recoding variables. This is BEFORE category groups are created and cleaned below.exp_tempandrev_tempare only temporary files; do not use for aggregating totals yet!
Update Agencies: Some agencies have merged with others or changed names over time.
# recodes old agency numbers to consistent agency number
exp_temp <- exp_temp %>%
mutate(agency = case_when(
(agency=="438"| agency=="475" |agency == "505") ~ "440",
# financial institution & professional regulation &
# banks and real estate --> coded as financial and professional reg
agency == "473" ~ "588", # nuclear safety moved into IEMA
(agency =="531" | agency =="577") ~ "532", # coded as EPA
(agency =="556" | agency == "538") ~ "406", # coded as agriculture
agency == "560" ~ "592", # IL finance authority (fire trucks and agriculture stuff)to state fire marshal
agency == "570" & fund == "0011" ~ "494", # city of Chicago road fund to transportation
TRUE ~ (as.character(agency)))) For aggregating revenue, use the rev_1998_2022 dataframe, join the funds_ab_in_2022 file to it, and then join the ioc_source_type file to the dataset. Remember: You need to update the funds_ab_in and ioc_source_type file every year!
# fund info to revenue for all years
rev_temp <- inner_join(rev_1998_2022, funds_ab_in_2022, by = "fund") %>% arrange(source)
# need to update the ioc_source_type file every year!
ioc_source_type <- readxl::read_xlsx("C:/Users/aleaw/OneDrive/Documents/PhD Fall 2021 - Spring 2022/Merriman RA/Fiscal Futures FY2022/Replication-Files/ioc_source_updated22_AWM.xlsx")
rev_temp <- left_join(rev_temp, ioc_source_type, by = "source")
# automatically used source, source name does not match for the join to work using source_name
# recodes old agency numbers to consistent agency number
rev_temp <- rev_temp %>%
mutate(agency = case_when(
(agency=="438"| agency=="475" |agency == "505") ~ "440",
# financial institution & professional regulation &
# banks and real estate --> coded as financial and professional reg
agency == "473" ~ "588", # nuclear safety moved into IEMA
(agency =="531" | agency =="577") ~ "532", # coded as EPA
(agency =="556" | agency == "538") ~ "406", # coded as agriculture
agency == "560" ~ "592", # IL finance authority (fire trucks and agriculture stuff)to state fire marshal
agency == "570" & fund == "0011" ~ "494", # city of Chicago road fund to transportation
TRUE ~ (as.character(agency)))) Overall Fiscal Health
Pension Discusion
For yearly expenditure calculations, the state contributions to the pension funds (object = 4431) should be the expenditure included for pensions. If trying to look at the bigger fiscal health picture and include unfunded liabilities and in/out flows, then items like purchase of investments and POB spikes in trends that occurred from policy changes should be analyzed and discussed in a separate section. Again, State contributions TO the pension funds are the expenditures BUT an additional graph/discussion on the employer contributions, employee contributions, and benefits paid out should be included and considered for additional context on Illinois’ situation.
Pension expenditures referenced in the analysis are based on state payments to the following pension systems:
• Teachers Retirement System (TRS)
- New POB bond in 2019: Accelerated Bond Fund paid benefits in advance
as lump sum
• State Employee Retirement System (SERS)
• State University Retirement System (SURS)
• Judges Retirement System (JRS)
• General Assembly Retirement System (GARS)
Additional context:
For the $10 billion in 2004, they borrowed money and invested it in
pension portfolio and hoped that the returns would be greater than the
interest on the debt. If returns>interest, then they increased the
pension funds and it was a good idea. Otherwise a short term band-aid
causes even more problems later. This added a significant amount to the
unfunded pension liabilities. In 2010 and 2011, POBs served as a type of
general borrowing for the state by borrowing against what was owed to
the pension systems and using that revenue that should have funded
pensions to instead subsidize the cost of providing core services.
Illinois borrowed money (POBs) and used it to pay for government
services. A temporary way to fill a budget gap for that 2010 that then
costs more in the long run due to increased unfunded liabilities and
interest on the borrowed money. - “Basket Case” by Dye 2015
In 2019 lawmakers offered a pension buyout plan where members could opt-out of their future benefits for a lump sum. However, few people participated in the buyout plan and very little savings have occurred so far. The buyout plan has been extended to 2026 in hopes that more people participate in it. Description of Pension Obligation Acceleration Bond at this link. Proceeds of bonds go into pension obligation acceleration fund (which are not included as a revenue source) and the fund is only used to make accelerated pension benefit payments. The pension stabilization fund (0319) is money put into the pension funds to help pay for unfunded liabilities from past poor budgeting decisions.
Data coding details
State pension contributions are largely captured with object=4431. (These are the State expenditures included in analysis)
- includes 8 billion payment in 2004 that creates large peak in
expenditure graphs
- Object 4431 does not capture recent pension stabilization fund which is fund = 0319, object = 1900 and has $300 million investment in FY2022.
- includes 8 billion payment in 2004 that creates large peak in
expenditure graphs
Fund=0475 is the Municipal Retirement Fund - Not included because state just helps collect and disperse local pension funds. IMRF is most funded pension fund in Illinois. Fund ends in 2015. All were considered purchase of investments.
IOC objects 1160-1165 are for all retirement expenditures for employers. These are not included in the analysis.
Some expenditures with object=4430 (benefits paid to retirees) were paid for with Pension obligation bond funds (fund == 0825).
In past years, some POB funded expenditures were moved to revenue side. Code logic was unclear. We are no longer doing this as of FY2021.
Other types of pension expenditures to consider when looking at pension funds: Pension obligation acceleration bond, state pension obligation bond reimbursements, pension pickup, accelerated pension buy-out (bond financed funds)
Other items to be aware of that may contain useful pension context:
- object = 1298 is for Purchase of Investments and is excluded from
analysis. In past analyses, there were a couple of exceptions during
2010 and 2011.
- Purchase of Investments captures the pension obligation bonds issued
in 2010-2011.
- Purchase of Investments captures the pension obligation bonds issued
in 2010-2011.
- object = 1900 for pension stabilization is under lump sums
- object = 1167 and 1168 is for Employer pension contributions but is
not used by IOC yet as of FY2022.
- object 4900 is awards and grants lump sum
Employer contributions for pensions are excluded from analysis to avoid double counting the cost of pensions. Expenditures with object 4430 for pensions, benefits, and annuities appears in items from funds 0473, 0477, 0479, 0481, (TRS, JRS, SERS, GARS), 0755, 0786, 0787, 0788, 0789, 0799 (deferred compensation plan, GAR excess benefit, JRS excess benefit, SER excess benefit, TRS excess benefit, state university retirement system) are NOT included in the analysis. All are coded with in_ff=0 in the fund_ab_in.xlsx file of funds.
# check what is being included in pensions
# funds related to pension contributions
pension_funds <- c("0472", "0473", "0477", "0479", "0481", "0755", "0786", "0787", "0788", "0789", "0799")
pension_check <- exp_temp %>%
mutate(pension = case_when(
# object == "4430" & fund == "0825" ~ "Object 4430 - Pension Buyout/Benefits Paid Early",
(object=="4430") ~ "Object 4430 - Benefits Paid to Employees; EXCLUDED", # pensions, annuities, benefits
(object=="4431") ~ "Object 4431 - State Contributions; INCLUDED", # 4431 = state payments into pension fund
(obj_seq_type > "11590000" & obj_seq_type < "11660000") ~ "Object 1160-1165 Employer Contributions to Pension Fund; EXCLUDED",
# objects 1159 to 1166 are all considered Retirement by Comptroller
TRUE ~ "0")) %>% # All other observations coded as 0 for non-pension items
# recodes specific instances of code anomalies from past years:
mutate(pension = case_when(
(object=="1298" & fund %in% pension_funds ) ~ "Object 1298 - Purchase of Investments; DROPPED",
# pension stabilization fund in 2022
# object == "1900" & fund == "0319" ~ "Fund 0319-Pension Stabilization",
object == "1900" & fund %in% pension_funds ~ "Fund 0319 - Pension Stabilization",
object == "4900" & fund %in% pension_funds ~ "Object 4900 - Awards/Grants; Weird 2010-2011 values",
TRUE ~ as.character(pension)) ) %>%
filter(pension != "0" )
pension_check %>% group_by(fy, pension) %>%
summarize(expenditure = sum(expenditure, na.rm = TRUE)) %>%
ggplot(aes(x=fy, y = expenditure, color = pension)) +
geom_line() +
labs (title = "Pension Fund Payments In and Retirement Benefits Out",
caption = "Object 4430 is retirement benefits paid to employees.
Object 4431 includes state payments INTO pension Fund.
Object 1998 is excluded except for years 2010 and 2011 due to POBs.")+
theme(legend.position = "bottom")+
guides(color = guide_legend(nrow=3))
pension_check %>% group_by(fy, object) %>%
summarize(expenditure = sum(expenditure, na.rm = TRUE)) %>%
ggplot(aes(x=fy, y = expenditure, color = object)) +
geom_line() +
labs (title = "Expenditures by Object")
pension_check %>% group_by(fy, type) %>%
summarize(expenditure = sum(expenditure, na.rm = TRUE)) %>%
ggplot(aes(x=fy, y = expenditure, color = type)) +
geom_line() +
labs (title = "Expenditures by Type", caption = "Not confident with what Type represents.
$10 billion POB issued in 2003-2004 and again in 2010-2011.")
pension_check3 <- exp_temp %>%
mutate(pension = case_when(
(object=="4430" ) ~ 1, # 4430 = pension benefits paid to retired employees
TRUE ~ 0)) %>%
filter(pension > 0 )
pension_check3 %>% group_by(fy) %>%
summarize(expenditure = sum(expenditure, na.rm = TRUE)) %>%
ggplot(aes(x=fy, y = expenditure)) +
geom_line() +
labs (title = "Pension Benefits Paid to Employees")
## taking care of Pension Obligation Bond proceedspension_picture <- exp_temp %>%
mutate(pension = case_when(
#object == "4430" & fund == "0825" ~ "Pension Buyout/Benefits Paid Early; INCLUDED",
(object=="4430") ~ "Benefits Paid to Employees", # pensions, annuities, benefits
(object=="4431") ~ "State Pension Contributions", # 4431 = state payments into pension fund
(obj_seq_type > "11590000" & obj_seq_type < "11660000") ~ "Employer Contributions",
# objects 1159 to 1166 are all considered Retirement by Comptroller
TRUE ~ "0")) %>% # All other observations coded as 0 for non-pension items
# recodes specific instances of code anomalies from past years:
# mutate(pension = case_when( (object=="1298" & fund %in% pension_funds ) ~ "Purchase of Investments",
# pension stabilization fund in 2022
# object == "1900" & fund == "0319" ~ "Fund 0319-Pension Stabilization",
# object == "1900" & fund %in% pension_funds ~ "Pension Stabilization Fund",
#TRUE ~ as.character(pension)) ) %>%
filter(pension != "0" )
pension_picture %>% group_by(fy, pension) %>%
summarize(expenditure = sum(expenditure, na.rm = TRUE)) %>%
ggplot(aes(x=fy, y = expenditure, color = pension)) +
geom_line() +
labs (title = "Pension Fund Payments In and Retirement Benefits Out",
caption = "")+
theme(legend.position = "bottom", legend.title = element_blank())#+ guides(color = guide_legend(nrow=2))
Employer contributions and state pension contributions are an example of an expenditure being double counted if both are included.
Pension Contributions
rev_type = 51 is for retirement/pension contributions from both employers and employees. These contributions from employers and employees are not considered a revenue source from the state and are excluded from the Fiscal Futures analysis. However, it is still important to understand the money going in and out of then pension funds even if it is not included as a yearly expenditure or revenue. Retirement contribution revenue is graphed below.
# current year employee revenue source = 0573, contributions by employee == 572 (stops at 2011)
retirement_contributions <- rev_temp %>%
filter(rev_type == "51") %>% group_by(fy) %>% summarize(contributions = sum(receipts))
employer_contributions <- rev_temp %>%
filter(rev_type == "51" & source == "0577") %>% group_by(fy) %>% summarize(contributions = sum(receipts))
employee_contributions <- rev_temp %>%
filter(rev_type == "51" & (source == "0572" | source == "0573") ) %>%
group_by(fy) %>% summarize(contributions = sum(receipts))
benefits_paid <- pension_check %>% filter(object == "4430") %>%
group_by(fy) %>%
summarize(expenditure = sum(expenditure, na.rm = TRUE))
state_contrib <- pension_check %>% filter(object == "4431") %>%
group_by(fy) %>%
summarize(expenditure = sum(expenditure, na.rm = TRUE))
rev_temp %>%
filter(rev_type == "51") %>% # all retirement contributions
group_by(fy, source) %>%
summarise(sum = sum(receipts, na.rm = TRUE)) %>%
ggplot() +
geom_line(aes(x=fy, y = sum, color=source)) + labs(title="All Retirement Contributions, ALL rev_source == 51")
rev_temp %>%
filter(rev_type == "51" & source == "0573" | source == "0572" | source == "0577") %>%
group_by(fy, source) %>%
summarise(sum = sum(receipts, na.rm = TRUE)) %>%
ggplot() +
geom_line(aes(x=fy, y = sum, color=source)) + labs(title="Retirement Contributions by Source from Employee and Employers", caption = "Source 0573, 0572 is for employee contributions. 0577 is Contributions by employer.")
# contributions and benefits paid comparison
ggplot()+
geom_line(data=employee_contributions, aes(x=fy, y=contributions), color="green") +
geom_line(data=employer_contributions, aes(x=fy, y=contributions), color="orange") +
geom_line(data= state_contrib, aes(x=fy, y = expenditure), color = "red")+
geom_line(data= benefits_paid, aes(x=fy, y = expenditure), color = "dark blue")+
labs(title="Pension fund inflows and outflows",
caption = "Blue: object = 4430 for benefits paid OUT of funds,
red: object = 4431 for state contributions into pension fund,
green:rev_type = 51 for employee contributions INTO the fund,
orange: employer contributions INTO the fund.", y = "Dollars")
pension_picture %>% group_by(fy, pension) %>%
summarize(expenditure = sum(expenditure, na.rm = TRUE)) %>%
ggplot(aes(x=fy, y = expenditure, color = pension)) +
geom_line() +
geom_line(data=employee_contributions, aes(x=fy, y=contributions), color="green") +
geom_line(data=employer_contributions, aes(x=fy, y=contributions), color="orange") +
labs (title = "Pension Fund Payments In and Retirement Benefits Out",
caption = "Neon green - employee contributions INTO the fund.
Orange - employer contributions INTO the fund.")+
theme(legend.position = "bottom", legend.title = element_blank())
Debt Service Discussion
Debt Service expenditures include interest payment on both short-term and long-term debt. We do not include escrow or principal payments.
Decision from Sept 30 2022: We are no longer including short term principal payments as a cost; only interest on borrowing is a cost. Pre FY22 and the FY21 correction, we did include an escrow payment and principle payments as costs but not bond proceeds as revenues. This caused expenditures to be inflated because we were essentially counting debt twice - the principle payment and whatever the money was spent on in other expenditure categories, which was incorrect.
Objects:
8813 interest INCLUDE AS COST
8811 is for principle EXCLUDE from aggregate debt
expenditure
8841 is for escrow payments EXCLUDE from aggregate debt
expenditure
8800 is for capital projects debt service (e.g. Build Illinois Bonds,
Civic Center, Tollway EXCLUDE tollway in debt cost -
Note: debt principle and interest are both included in capital projects
because they are combined in the data observations; bond proceeds are
not considered a revenue source. Can’t include capital projects interest
as easily as the GO bonds.
Obj_seq_type:
- Exclude: Bond principle payments: obj_seq_type == 88110008
- Exclude: Short term borrowing principle: obj_seq_type ==
88110108
- Include: General Obligation Bond Interest: obj_seq_type == 88130000
& 88130008
- Include: Interest on short-term borrowing: 88130108
- Exclude: Escrow payment == 88410008
- Include: Build IL Bonds, capital projects principal AND interest
- Tollway is obj_seq_type == 88000055, filter out fund == 0455 to remove
tollway
- fund == 0455 is the IL State Toll Highway fund, items mostly for
operations and maintenance
tollway <- exp_temp %>% filter(fund == "0455") #all tollway expenditures
capitalproject_debtservice <- exp_temp %>%filter(object == "8800") # ALL Capital projects debt service
# look at Illinois tollway bond proceeds and debt service:
# rev_temp %>% filter(fund == "0455") # examine items in fund 0455
#exp_temp %>% filter(fund == "0455") %>% group_by(fy) %>% summarize(sum = sum(expenditure)) %>% arrange(-fy)
tollway#rev_temp %>% filter(fund == "0455") %>% group_by(fy) %>% summarize(sum = sum(receipts)) %>% arrange(-fy)
tollway_exp <- exp_temp %>% filter(fund == "0455") %>% group_by(fy) %>% summarize(expenditure = sum(expenditure))
#tollway_exp %>% ggplot() + geom_line(aes(x=fy, y=expenditure)) + labs(title = "Fund 0455 from Expenditure: All Tollway Expenditures", caption = "Data from IOC Expenditure Files. Fund 0455 is the IL State Tollway")
# all tollway revenues, not just bond proceeds
alltollway<-rev_temp %>% filter(fund == "0455" & source != "0571") %>% group_by(fy) %>% summarize(sum = sum(receipts, na.rm = TRUE))
# tollway bond proceeds
tollway_bondproc <- rev_temp %>% filter(fund == "0455" & source == "0571" ) %>% group_by(fy) %>% summarize(sum = sum(receipts, na.rm = TRUE))
#alltollway %>% ggplot() + geom_line(aes(x=fy, y=sum)) + labs(title = "Fund 0455 - All Tollway Revenue", caption = "Data from IOC Revenue Files. Fund 0455 is the IL State Tollway Revenue")
#tollway_bondproc %>% ggplot() + geom_line(aes(x=fy, y=sum)) + labs(title = "Fund 0455 - Tollway Revenue: Tollway Bond Proceeds", caption = "Data from IOC Revenue Files. Fund 0455 is the IL State Tollway Revenue")
#ggplot() + geom_line(data=tollway_bondproc, aes(x=fy, y=sum)) + labs(title = "Fund 0455 - Tollway Revenue: Tollway Bond Proceeds", caption = "Data from IOC Revenue Files. Fund 0455 is the IL State Tollway Revenue")
#tollwaydebt %>% ggplot() + geom_line(aes(x=fy, y=sum)) + labs(title = "Tollway Debt Service", caption = "Debt service includes principal and interest for the Illinois Tollway. Object = 8800 and fund = 0455")
#tollway debt principal and interest
tollwaydebt <- exp_temp %>%filter(object == "8800" & fund == "0455") %>% group_by(fy) %>% summarize(sum=sum(expenditure))
# Tollway agency expenditures = SAME as filtering by fund == 0455
#tollway<-exp_temp %>% filter(agency == "557")
#exp_temp %>% filter(agency == "557") %>% group_by(fy) %>% summarize(sum = sum(expenditure)) %>% arrange(-fy)
# contributions and benefits paid comparison
ggplot()+
geom_line(data=tollway_bondproc, aes(x=fy, y=sum, color='Bond Proceeds')) +
geom_line(data= tollwaydebt, aes(x=fy, y = sum, color = 'Debt Service'))+
geom_line(data= tollway_exp, aes(x=fy, y = expenditure, color = 'Tollway Expenditures'))+
geom_line(data= alltollway, aes(x=fy, y = sum, color = "Tollway Revenue"))+
scale_color_manual(values = c(
'Bond Proceeds' = 'darkblue',
'Debt Service' = 'red',
'Tollway Expenditures' = 'orange',
'Tollway Revenue' = 'light green')) +
labs(title="Tollway bond procreeds, debt service, revenue, and expenditures.",
caption = "Intuitively, Tollway revenue + bond proceeds should be roughly equal to tollway expenditures + debt service.",
y = "Dollars")
Filtering for interest on short term borrowing and GO bonds (8813_ _
_ _) and GO bond principal amounts (88130008).
- object == 8813 is for interest but obj_seq_type is used just to be
more specific below.
Looking only at general obligation principal payments and interest payments:
# GO bond principal and GO bond interest
GObond_debt <- exp_temp %>%
filter(obj_seq_type == "88110008" |obj_seq_type == "88130000" | obj_seq_type == "88130008") %>%
group_by(fy, obj_seq_type) %>%
summarize(sum = sum(expenditure, na.rm=TRUE)) %>%
pivot_wider(names_from = obj_seq_type, values_from = sum) %>%
mutate(principal = `88110008`,
interest = sum(`88130008`+`88130000`, na.rm = TRUE),
ratio = (as.numeric(interest)/as.numeric(principal)))
GObond_debt %>% select(principal, interest, ratio) %>%
mutate(across(principal:interest, ~format(., big.mark= ",", scientific = F)))GObond_debt %>% ggplot() +
geom_line(aes(x=fy, y=principal, color = "Principal"))+
geom_line(aes(x=fy, y=interest, color = "Interest")) +
labs(title = "General Obligation principal and interest payments")
Looking only at short term borrowing principal and interest payments:
# short term borrowing, first observation is in 2004?
short_debt <- exp_temp %>%
filter(obj_seq_type == 88110108 |obj_seq_type == 88130108) %>%
group_by(fy, obj_seq_type) %>%
summarize(sum = sum(expenditure, na.rm=TRUE)) %>%
pivot_wider(names_from = obj_seq_type, values_from = sum) %>%
mutate(principal = `88110108`,
interest = `88130108`,
ratio = (as.numeric(interest)/as.numeric(principal)))
short_debt %>% select(principal, interest, ratio) %>%
mutate(across(principal:interest, ~format(., big.mark= ",", scientific = F)))short_debt %>% ggplot() +
geom_col(aes(x=fy, y=principal/1000000, fill = "Principal"))+
geom_col(aes(x=fy, y=interest/1000000, fill = "Interest")) +
labs(title = "Short Term Borrowing: Principal and Interest Payments")
When including short term borrowing and normal debt service, the debt ratio seems more normal and the total interest and principal payments over the years are smoothed out.
Principal and interest amounts calculated exclude the Illinois Tollway debt service and debt for capital projects. Capital projects debt service is examined below:
capitalprojects <- exp_temp %>%filter(object == "8800")
all_debt <- exp_temp %>%
filter(fund != "0455" & (object == "8811" |object == "8813" | object == "8800") )%>%
group_by(fy, object) %>%
summarize(sum = sum(expenditure, na.rm=TRUE)) %>%
pivot_wider(names_from = object, values_from = sum) %>%
mutate(principal = `8811`,
interest = `8813`,
CapitalProjects = `8800`,
ratio = (as.numeric(interest)/as.numeric(principal)))
all_debt %>% select(principal, interest, CapitalProjects, ratio) %>%
mutate(across(principal:CapitalProjects, ~format(., big.mark= ",", scientific = F)))all_debt %>% ggplot() +
geom_line(aes(x=fy, y=principal/1000000, color = "Principal"))+
geom_line(aes(x=fy, y=interest/1000000, color = "Interest"))+
geom_line(aes(x=fy, y = CapitalProjects / 1000000, color = "Capital Projects"))+
labs(y = "Debt ($Millions)",
title = "Principal and Interest payments", subtitle = "Principal and interest from short term borrowing and GO Bonds debt service", caption = "Capital projects does not include Illinois tollway debt service.
Capital projects include interest and principal values as one value and cannot be sepearated.")
Capital projects include the IL Civic Center and Build Illinois Bonds. Tollway principal and interest has been dropped from the State’s Debt Service expenditure but is counted in the Illinois Tollway Expenditure cost.
State Employee Healthcare Discussion
State Employee Health Care = Sum of expenditures for “health care coverage as elected by members per state employees group insurance act.” The payments are made from the Health Insurance Reserve Fund. Employee contributions are not considered a revenue source or an expenditure in our analysis.
Funding for the State Employees Group Insurance plan originates from two funds. The Health Insurance Reserve Fund (HIRF) and the Group Insurance Premium Fund (GIPF). Contributions and payment for Health coverage benefits are deposited INTO HIRF and contributions for life insurance are deposited into the GIPF.
HIRF is the fund mainly used to administer the group insurance program. Funding for HIRF comes from several different revenue sources, the General Revenue Fund (GRF), Road Fund, reimbursements, university funds, and misc funds. CGFA Report
Coding details
In FY2013, the Local Government Health Insurance fund was transferred to
the department of Central Management Services (agency changes from 478
to 416 in data.)
Employer group insurance contributions for health insurance are
excluded to avoid double counting the cost of healthcare provision. All
employer group insurance contributions are coded as object = 1180. BUT
the last two fiscal years were coded as 1900 instead of 1180 for lump
sums instead of employer contributions
- anything to do with pandemic money categorization?
Fund = 0907 = health insurance reserve, in_ff = 1
Fund = 0457 is “Group insurance premium”, in_ff = 1
Fund = 0193 is “Local govt health insurance reserve”, in=ff = 0
fund = 0477 is “Community College Health Insurance”, in=ff = 0.
- had large amount in early years
Fund = 9939 is “group self-insurers’ insolv”, in_ff = 1
Fund = 0940 is Self-Insurers security, in_ff = 0
Fund = 0739 is Group Workers Comp Pool Insol, in_ff = 1
- eehc = 0 means it is NOT a state healthcare cost but it is an
employer contribution of some type to some fund
- eehc = 1 means it is a state employee healthcare cost and it is an employer contribution to health insurance
health_ins_reserve <- exp_temp %>% filter(fund == "0907") %>% group_by(fy) %>%
summarize(fund_0907 = sum(expenditure))
health_ins_reserve %>%
ggplot(aes(x=fy, y=fund_0907)) + geom_line() + labs(title="Health Insurance Reserve", subtitle = "Sum of expenditures from fund 907")
# object 1180 is inconsistently coded over time form the IOC
# object 1180 should be employer contributions to healthcare group insurance
employer_contributions <- exp_temp %>% filter(object == "1180") %>% group_by(fy) %>% summarize(object1180 = sum(expenditure))
employer_contributions%>%
ggplot(aes(x=fy, y=object1180)) + geom_line() + labs(title="Employer Contributions to Healthcare Group Insurance, IOC Object 1180")
employer_contributions2 <- exp_temp %>% filter(object == "1180" & fund=="0001") %>% group_by(fy) %>% summarize(object1180 = sum(expenditure))
employer_contributions2 %>%
ggplot(aes(x=fy, y=object1180)) + geom_line() + labs(title="Employer Contributions to Healthcare Group Insurance", subtitle = "IOC Object 1180 from Fund 001")
# examine combined group insurance totals per year
group_ins2 <- exp_temp %>%
mutate(eehc = ifelse(
# group insurance contributions for 1998-2005 and 2013-present
# CMS took over health insurance in 2013
fund == "0001" & (object == "1180" | object =="1900") & agency == "416" & appr_org=="20", 1, 0) )%>%
mutate(eehc = ifelse(
# group insurance contributions for 2006-2012
# health insurance was in healthcare and family services, agency 478 for a few years
fund == "0001" & object == "1180" & agency == "478" & appr_org=="80", 1, eehc) )%>%
filter(eehc == 1) %>%
group_by(fy) %>%
summarize(dropped_group_premiums = sum(expenditure, na.rm=TRUE))
group_ins2 %>% ggplot(aes(x=fy, y=dropped_group_premiums)) + geom_line() + labs(title="Employer Healthcare Group Insurance Contributions", subtitle= " - Dropped from analysis to avoid double counting healthcare expenditures", caption = "Objects 1180 and 1900 from fund 0001. See code for additional coding details.")
If observation is a group insurance contribution, then the expenditure amount is set to $0 (essentially dropped from analysis).
Health Insurance - Revenue Side
Employee insurance premiums for healthcare are a revenue source for the state in the IOC data but are NOT included in the Fiscal Futures analysis and fiscal gap calculations.
Source #’s:
0120 = ins prem-option life
0120 = ins prem-optional life/univ
0347 = optional health - HMO
0348 = optional health - dental
0349 = optional health - univ/local SI
0350 = optional health - univ/local
0351 = optional health - retirement
0352 = optional health - retirement SI
0353 = optional health - retire/dental
0354 = optional health - retirement hmo
2199-2209 = various HMOs, dental, health plans from Health Insurance Reserve (fund)
health_insurance_fund_rev<- rev_temp %>%
filter(fund=="0907") %>%
group_by(fy) %>%
summarize(health_ins_rev = sum(receipts))
health_insurance_fund_rev %>%
ggplot(aes(x=fy, y = health_ins_rev)) +
geom_line() + labs( title = "Health insurance fund - All revenue, Fund 0907")
#collect optional insurance premiums to fund 0907 for use in eehc expenditure
employee_health_premiums <- rev_temp %>%
mutate(employee_premiums = ifelse(
fund=="0907" & (source=="0120"| source=="0121"| (source>"0345" & source<"0357")|(source>"2199" & source<"2209")), 1, 0)) %>%
filter(employee_premiums == 1)
# optional insurance premiums = employee insurance premiums
emp_premium <- employee_health_premiums %>%
group_by(fy) %>%
summarize(employee_premiums_sum = sum(receipts))
emp_premium %>% ggplot(aes(x=fy, y = employee_premiums_sum)) +
geom_line() + labs( title = "Employee health insurance premiums")
# contributions and benefits paid comparison
ggplot()+
# geom_line(data=group_ins, aes(x=fy, y=object1180, color='Group Insurance1')) +
geom_line(data=health_insurance_fund_rev, aes(x=fy, y=health_ins_rev, color='Health Insurance Fund - All Revenue')) +
geom_line(data = emp_premium, aes(x=fy, y = employee_premiums_sum, color = 'Revenue from Employee Premiums')) +
geom_line(data=health_ins_reserve, aes(x=fy, y=fund_0907, color='Cost of Provision')) +
geom_line(data=employer_contributions, aes(x=fy, y=object1180, color='Group Insurance-Object1180')) +
# geom_line(data=employer_contributions2, aes(x=fy, y=object1180, color='Employer Contributions-General Fund')) +
geom_line(data=group_ins2, aes(x=fy, y=dropped_group_premiums, color='Group Insurance - 1180 & 1900')) +
#geom_line(data= healthcare_costs, aes(x=fy, y = cost_of_provision, color = 'Healthcare Costs'))+
scale_color_manual(values = c(
'Cost of Provision' = 'darkblue',
'Health Insurance Fund - All Revenue' = 'light green',
'Revenue from Employee Premiums' = 'dark green',
'Group Insurance - 1180 & 1900' = 'blue',
'Group Insurance-Object1180' = 'light blue'
# 'Employer Contributions-General Fund' = 'light blue'
)) +
labs(title="Healthcare costs and group insurance contributions",
caption = "Healthcare costs and group insurance contributions",
y = "Dollars", x = "")
exp_temp <- exp_temp %>%
mutate(eehc = ifelse(object == "1180", 1, 0)) %>%
mutate(eehc = ifelse((eehc == 1 & in_ff =="0"), 2, eehc))
#%>%mutate(in_ff = ifelse(eehc == 2, "1", in_ff) ) %>% filter(eehc ==2 )
table(exp_temp$eehc)##
## 0 1 2
## 163568 4414 149Expenditure & Revenue Categorization
Modify Expenditure File
Tax refunds
Aggregate expenditures: Save tax refunds as negative revenue. Code refunds to match the rev_type codes (02=income taxes, 03 = corporate income taxes, 06=sales tax, 09=motor fuel tax, 24=insurance taxes and fees, 35 = all other tax refunds).
## negative revenue becomes tax refunds
tax_refund_long <- exp_temp %>% # fund != "0401" # removes State Trust Funds
filter(fund != "0401" & (object=="9910"|object=="9921"|object=="9923"|object=="9925")) %>%
# keeps these objects which represent revenue, insurance, treasurer,and financial and professional reg tax refunds
mutate(refund = case_when(
fund=="0278" & sequence == "00" ~ "02", # for income tax refund
fund=="0278" & sequence == "01" ~ "03", # tax administration and enforcement and tax operations become corporate income tax refund
fund == "0278" & sequence == "02" ~ "02",
object=="9921" ~ "21", # inheritance tax and estate tax refund appropriation
object=="9923" ~ "09", # motor fuel tax refunds
obj_seq_type == "99250055" ~ "06", # sales tax refund
fund=="0378" & object=="9925" ~ "24", # insurance privilege tax refund
fund=="0001" & object=="9925" ~ "35", # all other taxes
T ~ "CHECK")) # if none of the items above apply to the observations, then code them as CHECK
exp_temp <- left_join(exp_temp, tax_refund_long) %>%
mutate(refund = ifelse(is.na(refund),"not refund", as.character(refund)))
tax_refund <- tax_refund_long %>%
group_by(refund, fy)%>%
summarize(refund_amount = sum(expenditure, na.rm = TRUE)/1000000) %>%
pivot_wider(names_from = refund, values_from = refund_amount, names_prefix = "ref_") %>%
mutate_all(~replace_na(.,0)) %>%
arrange(fy)
tax_refund %>% pivot_longer( ref_02:ref_35, names_to = "Refund Type", values_to = "Amount") %>%
ggplot()+
geom_line(aes(x=fy,y=Amount, group = `Refund Type`, color = `Refund Type`))+
labs(title = "Refund Types", caption = "Refunds are excluded from Expenditure totals and instead subtracted from Revenue totals") +
labs(title = "Tax refunds",
caption = "Rev_type codes: 02=income taxes, 03=corporate income taxes, 06=sales tax, 09=motor fuel tax,
24=insurance taxes and fees, 35 = all other tax refunds." )
# remove the items we recoded in tax_refund_long
exp_temp <- exp_temp %>% filter(refund == "not refund")tax_refund amounts are removed from expenditure totals
and subtracted from revenue totals (since they were tax refunds).
Pension Expenditures
Earlier I argued that pension_rev should be
subtracted from the state pension expenditures because employee
contributions to pensions were a revenue source. If we wanted net
pension cost, then we should subtract employee contributions from
pension costs BUT as of 11/30/2022, I have changed my mind. All rev==51
pension contributions should be excluded from analysis BUT pensions as a
whole are discussed in the ‘Pension Discussion’ section earlier in the
document.
For yearly expenditure calculations, the state contributions to the pension funds (object = 4431) should be the expenditure included for pensions. If trying to look at the bigger fiscal health picture and include unfunded liabilities and in/out flows, then items like purchase of investments and large spikes that occurred from policy changes should be included. Again, State contributions TO the pension funds are the expenditures BUT an additional graph/discussion on the employer contributions, employee contributions (revenue source), and benefits paid out to employees (object = 4430) should be included and considered for additional context on Illinois’ situation.
State payments to the following pension systems:
• Teachers Retirement System (TRS)
- New POB bond in 2019: Accelerated Bond Fund paid benefits in advance
as lump sum
• State Employee Retirement System (SERS)
• State University Retirement System (SURS)
• Judges Retirement System (JRS)
• General Assembly Retirement System (GARS)
- Includes pension stabilization fund = 0319, object = 1900 and the
$300 million investment in FY2022.
- State pension contributions are largely captured with object=4431.
(State payments into pension fund)
- includes 8 billion payment in 2004 that creates large peak in
expenditure graphs
- does not capture recent pension stabilization payments
- includes 8 billion payment in 2004 that creates large peak in
expenditure graphs
- Some expenditures with object=4430 are paid for with Pension obligation bond funds (fund == 0825). In past years, some POB funded expenditures were moved to revenue side in the Stata code. We are no longer doing this as of FY2021.
Modify exp_temp and move all state pension contributions to their own group (901):
exp_temp <- exp_temp %>%
arrange(fund) %>%
mutate(pension = case_when(
(object=="4431") ~ 1, # 4431 = easy to find pension payments INTO fund
# (object>"1159" & object<"1166") & fund != "0183" & fund != "0193" ~ 2,
# objects 1159 to 1166 are all considered Retirement by Comptroller,
# Excluded - employer contributions from agencies/organizations/etc.
(object=="1298" & # Purchase of Investments, Normally excluded
(fy==2010 | fy==2011) &
(fund=="0477" | fund=="0479" | fund=="0481")) ~ 3, #judges retirement OUT of fund
# state borrowed money from pension funds to pay for core services during 2010 and 2011.
# used to fill budget gap and push problems to the future.
fund == "0319" ~ 4, # pension stabilization fund
TRUE ~ 0) )
table(exp_temp$pension) ##
## 0 1 3 4
## 167723 228 6 5exp_temp %>% filter(pension != 0) %>%
mutate(pension = as.factor(pension))%>%
group_by(fy, pension) %>%
summarize(expenditure = sum(expenditure, na.rm = TRUE)) %>%
ggplot(aes(x=fy, y = expenditure, group=pension)) +
geom_line(aes(color = pension)) +
labs (title = "Pension expenditures",
caption = "1 = State contributions INTO pension funds")+
theme(legend.position = "bottom")
# special accounting of pension obligation bond (POB)-funded contributions to JRS, SERS, GARS, TRS
exp_temp <- exp_temp %>%
# change object for 2010 and 2011, retirement expenditures were bond proceeds and would have been excluded
mutate(object = ifelse((pension >0 & in_ff == "0"), "4431", object)) %>%
# changes weird teacher & judge retirement system pensions object to normal pension object 4431
mutate(pension = ifelse(pension >0 & in_ff == "0", 6, pension)) %>% # coded as 6 if it was supposed to be excluded.
mutate(in_ff = ifelse(pension>0, "1", in_ff))
table(exp_temp$pension) ##
## 0 1 4 6
## 167723 226 5 8# all other pensions objects codes get agency code 901 for State Pension Contributions
exp_temp <- exp_temp %>%
mutate(agency = ifelse(pension>0, "901", as.character(agency)),
agency_name = ifelse(agency == "901", "State Pension Contributions", as.character(agency_name)))
exp_temp %>%
filter(pension > 0) %>%
mutate(pension = as.factor(pension)) %>%
group_by(fy, pension) %>%
summarize(expenditure = sum(expenditure, na.rm=TRUE)) %>%
ggplot(aes(x=fy, y=expenditure, color = pension)) +
geom_line() +
labs (title = "Pension Expenditures",
caption = "")
exp_temp %>%
filter(pension > 0) %>%
group_by(fy) %>%
summarize(expenditure = sum(expenditure, na.rm=TRUE)) %>%
ggplot(aes(x=fy, y=expenditure)) +
geom_line() +
labs (title = "Pension Expenditures")
Drop Interfund transfers
- object == 1993 is for interfund cash transfers
- agency == 799 is for statutory transfers
- object == 1298 is for purchase of investments and is not spending EXCEPT for costs in 2010 and 2011 (and were recoded already to object == “4431”). Over 168,000 observations remain.
transfers_drop <- exp_temp %>% filter(
agency == "799" | # statutory transfers
object == "1993" | # interfund cash transfers
object == "1298") # purchase of investments
exp_temp <- anti_join(exp_temp, transfers_drop)
exp_tempState employee healthcare costs
State Employee Health Care = Sum of expenditures for “health care coverage as elected by members per state employees group insurance act.” The payments are made from the Health Insurance Reserve Fund. We subtract the share that came from employee contributions. Employee contributions are not considered a revenue source or an expenditure in our analysis.
In FY2013, the Local Government Health Insurance fund was transferred to the department of Central Management Services (agency changes from 478 to 416 in data.)
Employer group insurance contributions for health insurance are
excluded to avoid double counting the cost of healthcare. All employer
group insurance contributions are coded as object = 1180. BUT the last
two fiscal years were coded as 1900 instead of 1180 for lump sums
instead of employer contributions
- anything to do with pandemic money categorization?
Fund = 0457 is “Group insurance premium”, in_ff = 1
Fund = 0193 is “Local govt health insurance reserve”, in=ff = 0
fund = 0477 is “Community College Health Insurance”, in=ff = 0.
- had large amount in early years
Fund = 0907 = health insurance reserve, in_ff = 1
Fund = 9939 is “group self-insurers’ insolv”, in_ff = 1
Fund = 0940 is Self-Insurers security, in_ff = 0
Fund = 0739 is Group Workers Comp Pool Insol, in_ff = 1
- eehc = 0 means it is NOT a state healthcare cost but it is an
employer contribution of some type to some fund
- eehc = 1 means it is a state employee healthcare cost and it is an employer contribution to health insurance
Of the many different ways I have coded the healthcare costs, most do not have expenditures during 2010, 2011, 2016, 2017.
If observation is a group insurance contribution, then the expenditure amount is set to $0 (essentially dropped from analysis).
#if observation is a group insurance contribution, then the expenditure amount is set to $0 (essentially dropped from analysis)
# pretend eehc is named group_insurance_contribution or something like that
# eehc coded as zero implies that it is group insurance
# if eehc=0, then expenditures are coded as zero for group insurance to avoid double counting costs
exp_temp <- exp_temp %>%
mutate(eehc = ifelse(
# group insurance contributions for 1998-2005 and 2013-present
fund == "0001" & (object == "1180" | object =="1900") & agency == "416" & appr_org=="20", 0, 1) )%>%
mutate(eehc = ifelse(
# group insurance contributions for 2006-2012
fund == "0001" & object == "1180" & agency == "478" & appr_org=="80", 0, eehc) )%>%
# group insurance contributions from road fund
# coded with 1900 for some reason??
mutate(eehc = ifelse(
fund == "0011" & object == "1900" & agency == "416" & appr_org=="20", 0, eehc) ) %>%
mutate(expenditure = ifelse(eehc=="0", 0, expenditure)) %>%
mutate(agency = case_when( # turns specific items into State Employee Healthcare (agency=904)
fund=="0907" & (agency=="416" & appr_org=="20") ~ "904", # central management Bureau of benefits using health insurance reserve
fund=="0907" & (agency=="478" & appr_org=="80") ~ "904", # agency = 478: healthcare & family services using health insurance reserve - stopped using this in 2012
TRUE ~ as.character(agency))) %>%
mutate(agency_name = ifelse(agency == "904", "STATE EMPLOYEE HEALTHCARE", as.character(agency_name)),
in_ff = ifelse( agency == "904", 1, in_ff),
group = ifelse(agency == "904", "904", as.character(agency)))
# creates group variable
# Default group = agency number
healthcare_costs <- exp_temp %>% filter(group == "904")
healthcare_costsexp_temp %>% filter(group == "904") %>% group_by(fy) %>%
summarise(healthcare_cost = sum(expenditure, na.rm = TRUE)) %>%
ggplot() +geom_line(aes(x=fy, y=healthcare_cost)) + labs(title="State Employee Healthcare Costs - Included in Fiscal Futures Model", caption = "Fund 0907 for agencies responsible for health insurance reserve (DHFS & CMS)")
#exp_temp <- anti_join(exp_temp, healthcare_costs) %>% mutate(expenditure = ifelse(object == "1180", 0, expenditure))
#healthcare_costs_yearly <- healthcare_costs %>% group_by(fy, group) %>% summarise(healthcare_cost = sum(expenditure, na.rm = TRUE)/1000000) %>% select(-group)This code chunk above for dealing with group insurance means
that healthcare costs need to be added to expenditures after other group
names are assigned. Then employee contributions/insurance premiums from
the revenue side need to be subtracted from the total cost of employee
healthcare for the net cost. Do not do this.
Local Transfers
Separate transfers to local from parent agencies that come from DOR(492) or Transportation (494). Treats muni revenue transfers as expenditures, not negative revenue.
The share of certain taxes levied state-wide at a common rate and then transferred to local governments. (Purely local-option taxes levied by specific local governments with the state acting as collection agent are NOT included.)
The five corresponding revenue items are:
• Local share of Personal Income Tax
• Local share of General Sales Tax
• Personal Property Replacement Tax on Business Income
• Personal Property Replacement Tax on Public Utilities
• Local share of Motor Fuel Tax - Transportation Renewal Fund 0952
Notes - these don’t exit: - fund==“0627” & object==“4472”~ “976”
# public transportation
- fund==“0648” & object==“4472” ~ “976”,
exp_temp <- exp_temp %>% mutate(
agency = case_when(fund=="0515" & object=="4470" & type=="08" ~ "971", # income tax to local governments
fund=="0515" & object=="4491" & type=="08" & sequence=="00" ~ "971", # object is shared revenue payments
fund=="0802" & object=="4491" ~ "972", #pprt transfer
fund=="0515" & object=="4491" & type=="08" & sequence=="01" ~ "976", #gst to local
fund=="0627" & object=="4472"~ "976" , # public transportation fund but no observations exist
fund=="0648" & object=="4472" ~ "976", # downstate public transportation, but doesn't exist
fund=="0515" & object=="4470" & type=="00" ~ "976", # object 4470 is grants to local governments
object=="4491" & (fund=="0188"|fund=="0189") ~ "976",
fund=="0187" & object=="4470" ~ "976",
fund=="0186" & object=="4470" ~ "976",
object=="4491" & (fund=="0413"|fund=="0414"|fund=="0415") ~ "975", #mft to local
fund == "0952"~ "975", # Added Sept 29 2022 AWM. Transportation Renewal MFT
TRUE ~ as.character(agency)),
agency_name = case_when(agency == "971"~ "INCOME TAX 1/10 TO LOCAL",
agency == "972" ~ "PPRT TRANSFER TO LOCAL",
agency == "975" ~ "MFT TO LOCAL",
agency == "976" ~ "GST TO LOCAL",
TRUE~as.character(agency_name)),
group = ifelse(agency>"970" & agency < "977", as.character(agency), as.character(group)))transfers_long <- exp_temp %>%
filter(group == "971" |group == "972" | group == "975" | group == "976")
transfers_long %>%
group_by(agency_name, group, fy) %>%
summarize(expenditure = sum(expenditure, na.rm=TRUE) )%>%
ggplot() + geom_line(aes(x=fy, y = expenditure, color=agency_name)) + labs(title = "Transfers to Local Governments", caption = "Data Source: Illinois Office of the Comptroller")
transfers <- transfers_long %>%
group_by(fy, group ) %>%
summarize(sum_expenditure = sum(expenditure)/1000000) %>%
pivot_wider(names_from = "group", values_from = "sum_expenditure", names_prefix = "exp_" )
exp_temp <- anti_join(exp_temp, transfers_long)
dropped_inff_0 <- exp_temp %>% filter(in_ff == 0)
exp_temp <- exp_temp %>% filter(in_ff == 1) # drops in_ff = 0 funds AFTER dealing with net-revenue aboveDebt Service
Debt Service expenditures include interest payment on both short-term and long-term debt. We do not include escrow or principal payments.
Decision from Sept 30 2022: We are no longer including short term principal payments as a cost; only interest on borrowing is a cost. Pre FY22 and the FY21 correction, we did include an escrow payment and principle payments as costs but not bond proceeds as revenues. This caused expenditures to be inflated because we were essentially counting debt twice - the principle payment and whatever the money was spent on in other expenditure categories, which was incorrect.
debt_drop <- exp_temp %>%
filter(object == "8841" | object == "8811")
# escrow OR principle
#debt_drop %>% group_by(fy) %>% summarize(sum = sum(expenditure)) %>% arrange(-fy)
debt_keep <- exp_temp %>%
filter(fund != "0455" & (object == "8813" | object == "8800" ))
# examine the debt costs we want to include
#debt_keep %>% group_by(fy) %>% summarize(sum = sum(expenditure)) %>% arrange(-fy)
exp_temp <- anti_join(exp_temp, debt_drop)
exp_temp <- anti_join(exp_temp, debt_keep)
debt_keep <- debt_keep %>%
mutate(
agency = ifelse(fund != "0455" & (object == "8813" | object == "8800"), "903", as.character(agency)),
group = ifelse(fund != "0455" & (object == "8813" | object == "8800"), "903", as.character(group)),
in_ff = ifelse(group == "903", 1, as.character(in_ff)))
debt_keep_yearly <- debt_keep %>% group_by(fy, group) %>% summarize(debt_cost = sum(expenditure,na.rm=TRUE)/1000000) %>% select(-group)Medicaid
Medicaid. That portion of the Healthcare and Family Services (or Public Aid in earlier years, agency code 478) budget for Medical (appr_organization code 65) for awards and grants (object codes 4400 and 4900).
State CURE will remain in the Medicaid expenditure category due to the nature of it being federal funds providing public health services and funding to locations that provide public services.
- Uses same appropriation name of “HEALTHCARE PROVIDER RELIEF” and fund == 0793 and obj_seq_type == 49000000. So can defend the “mistake” of including healthcare provider relief as Medicaid expenditure.
Add Other Fiscal Future group codes
exp_temp <- exp_temp %>%
#mutate(agency = as.numeric(agency) ) %>%
# arrange(agency)%>%
mutate(
group = case_when(
agency>"100"& agency<"200" ~ "910", # legislative
agency == "528" | (agency>"200" & agency<"300") ~ "920", # judicial
pension>0 ~ "901", # pensions
(agency>"309" & agency<"400") ~ "930", # elected officers
agency == "586" ~ "959", # create new K-12 group
agency=="402" | agency=="418" | agency=="478" | agency=="444" | agency=="482" ~ as.character(agency), # aging, CFS, HFS, human services, public health
T ~ as.character(group))
) %>%
mutate(group = case_when(
agency=="478" & (appr_org=="01" | appr_org == "65" | appr_org=="88") & (object=="4900" | object=="4400") ~ "945", # separates CHIP from health and human services and saves it as Medicaid
agency == "586" & fund == "0355" ~ "945", # 586 (Board of Edu) has special education which is part of medicaid
# OLD CODE: agency == "586" & appr_org == "18" ~ "945", # Spec. Edu Medicaid Matching
agency=="425" | agency=="466" | agency=="546" | agency=="569" | agency=="578" | agency=="583" | agency=="591" | agency=="592" | agency=="493" | agency=="588" ~ "941", # public safety & Corrections
agency=="420" | agency=="494" | agency=="406" | agency=="557" ~ as.character(agency), # econ devt & infra, tollway
agency=="511" | agency=="554" | agency=="574" | agency=="598" ~ "946", # Capital improvement
agency=="422" | agency=="532" ~ as.character(agency), # environment & nat. resources
agency=="440" | agency=="446" | agency=="524" | agency=="563" ~ "944", # business regulation
agency=="492" ~ "492", # revenue
agency == "416" ~ "416", # central management services
agency=="448" & fy > 2016 ~ "416", #add DoIT to central management
T ~ as.character(group))) %>%
mutate(group = case_when(
# agency=="684" | agency=="691" ~ as.character(agency), # moved under higher reducation in next line. 11/28/2022 AWM
agency=="692" | agency=="695" | agency == "684" |agency == "691" | (agency>"599" & agency<"677") ~ "960", # higher education
agency=="427" ~ as.character(agency), # employment security
agency=="507"| agency=="442" | agency=="445" | agency=="452" |agency=="458" | agency=="497" ~ "948", # other departments
# other boards & Commissions
agency=="503" | agency=="509" | agency=="510" | agency=="565" |agency=="517" | agency=="525" | agency=="526" | agency=="529" | agency=="537" | agency=="541" | agency=="542" | agency=="548" | agency=="555" | agency=="558" | agency=="559" | agency=="562" | agency=="564" | agency=="568" | agency=="579" | agency=="580" | agency=="587" | agency=="590" | agency=="527" | agency=="585" | agency=="567" | agency=="571" | agency=="575" | agency=="540" | agency=="576" | agency=="564" | agency=="534" | agency=="520" | agency=="506" | agency == "533" ~ "949",
# non-pension expenditures of retirement funds moved to "Other Departments"
# should have removed pension expenditures already from exp_temp in Pensions step above
agency=="131" | agency=="275" | agency=="589" |agency=="593"|agency=="594"|agency=="693" ~ "948",
T ~ as.character(group))) %>%
mutate(group_name =
case_when(
group == "416" ~ "Central Management",
group == "478" ~ "Healthcare and Family Services",
group == "482" ~ "Public Health",
group == "900" ~ "NOT IN FRAME",
group == "901" ~ "STATE PENSION CONTRIBUTION",
group == "903" ~ "DEBT SERVICE",
group == "910" ~ "LEGISLATIVE" ,
group == "920" ~ "JUDICIAL" ,
group == "930" ~ "ELECTED OFFICERS" ,
group == "940" ~ "OTHER HEALTH-RELATED",
group == "941" ~ "PUBLIC SAFETY" ,
group == "942" ~ "ECON DEVT & INFRASTRUCTURE" ,
group == "943" ~ "CENTRAL SERVICES",
group == "944" ~ "BUS & PROFESSION REGULATION" ,
group == "945" ~ "MEDICAID" ,
group == "946" ~ "CAPITAL IMPROVEMENT" ,
group == "948" ~ "OTHER DEPARTMENTS" ,
group == "949" ~ "OTHER BOARDS & COMMISSIONS" ,
group == "959" ~ "K-12 EDUCATION" ,
group == "960" ~ "UNIVERSITY EDUCATION" ,
group == agency ~ as.character(group),
TRUE ~ "Check name"),
year = fy)
exp_temp %>% filter(group_name == "Check name")#write_csv(exp_temp, "all_expenditures_recoded.csv")All expenditures recoded but not aggregated: Allows for inspection of individual expenditures within larger categories. This stage of the data is extremely useful for investigating almost all questions we have about the data.
Note that these are the raw figures BEFORE we take the additional steps:
- Subtract tax refunds from tax revenues by revenue type.
Modify Revenue data
Revenue Categories NOT included in Fiscal Futures:
- 32. Garnishment-Levies. (State is fiduciary, not beneficiary.)
- 45. Student Fees-Universities. (Excluded from state-level
budget.)
- 51. Retirement Contributions (of individuals and non-state
entities).
- 66. Proceeds, Investment Maturities. (Not sustainable flow.)
- 72. Bond Issue Proceeds. (Not sustainable flow.)
- 75. Inter-Agency Receipts.
- 79. Cook County Intergovernmental Transfers. (State is not
beneficiary.)
- 98. Prior Year Refunds.
- 99. Statutory Transfers.
All Other Sources
Expanded to include the following smaller sources:
- 30. Horse Racing Taxes & Fees.
- 60. Other Grants and Contracts.
- 63. Investment Income.
For aggregating revenue, use the rev_1998_2022 dataframe, join the funds_ab_in_2022 file to it, and then join the ioc_source_type file to the dataset. Remember: You need to update the funds_ab_in and ioc_source_type file every year!
# fund info to revenue for all years
rev_temp <- inner_join(rev_1998_2022, funds_ab_in_2022, by = "fund") %>% arrange(source)
# need to update the ioc_source_type file every year!
ioc_source_type <- readxl::read_xlsx("C:/Users/aleaw/OneDrive/Documents/PhD Fall 2021 - Spring 2022/Merriman RA/Fiscal Futures FY2022/Replication-Files/ioc_source_updated22_AWM.xlsx")
rev_temp <- left_join(rev_temp, ioc_source_type, by = "source")
# automatically used source, source name does not match for the join to work using source_name
# recodes old agency numbers to consistent agency number
rev_temp <- rev_temp %>%
mutate(agency = case_when(
(agency=="438"| agency=="475" |agency == "505") ~ "440",
# financial institution & professional regulation &
# banks and real estate --> coded as financial and professional reg
agency == "473" ~ "588", # nuclear safety moved into IEMA
(agency =="531" | agency =="577") ~ "532", # coded as EPA
(agency =="556" | agency == "538") ~ "406", # coded as agriculture
agency == "560" ~ "592", # IL finance authority (fire trucks and agriculture stuff)to state fire marshal
agency == "570" & fund == "0011" ~ "494", # city of Chicago road fund to transportation
TRUE ~ (as.character(agency)))) Federal to State Transfers
rev_temp <- rev_temp %>% filter(in_ff==1)
rev_temp <- rev_temp %>%
mutate(
rev_type = ifelse(rev_type=="57" & agency=="478" & (source=="0618"|source=="2364"|source=="0660"|source=="1552"| source=="2306"| source=="2076"|source=="0676"|source=="0692"), "58", rev_type),
rev_type_name = ifelse(rev_type=="58", "Federal Medicaid Reimbursements", rev_type_name),
rev_type = ifelse(rev_type=="57" & agency=="494", "59", rev_type),
rev_type_name = ifelse(rev_type=="59", "Federal Transportation", rev_type_name),
rev_type_name = ifelse(rev_type=="57", "Federal - Other", rev_type_name),
rev_type = ifelse(rev_type=="6", "06", rev_type),
rev_type = ifelse(rev_type=="9", "09", rev_type))
rev_temp %>%
filter(rev_type == "58" | rev_type == "59" | rev_type == "57") %>%
group_by(fy, rev_type, rev_type_name) %>%
summarise(receipts = sum(receipts, na.rm = TRUE)/1000000) %>%
ggplot() +
geom_line(aes(x=fy, y=receipts,color=rev_type_name)) +
theme_bw() +
scale_y_continuous(labels = comma)+
labs(title = "Federal to State Transfers",
y = "Millions of Dollars", x = "") +
theme(legend.position = "bottom", legend.title = element_blank() )
Looking at Federal Revenue:
rev_temp %>% filter(rev_type == "57" & fy == 2021) %>% group_by(source_name_AWM) %>% summarize(receipts =sum(receipts)) %>% arrange(-receipts)rev_temp %>% filter(rev_type == "57" & fy == 2021 & source_name_AWM == "FEDERAL STIMULUS PACKAGE") %>% group_by(fund_name) %>% summarize(receipts =sum(receipts)) %>% arrange(-receipts)exp_temp %>% filter(fy == 2022 & (fund_name == "STATE CURE" | fund_name == "LOCAL CURE")) %>% group_by(org_name, agency_name, wh_approp_name, fund_name) %>% summarize(sum=sum(expenditure)) %>% arrange(-sum)exp_temp %>% filter(fy == 2022 & (fund_name == "STATE CURE" | fund_name == "LOCAL CURE")) %>% group_by(org_name, agency_name, object, wh_approp_name, fund_name) %>% summarize(sum=sum(expenditure)) %>% arrange(-sum)exp_temp %>% filter(fy == 2022 & (fund_name == "STATE CURE" | fund_name == "LOCAL CURE")) %>% group_by(agency_name, object, wh_approp_name, fund_name) %>% summarize(sum=sum(expenditure)) %>% arrange(-sum)exp_temp %>% filter(fy == 2022 & (fund_name == "STATE CURE" | fund_name == "LOCAL CURE")) %>% group_by(fund_name, object, org_name) %>% summarize(sum=sum(expenditure)) %>% arrange(-sum)exp_temp %>% filter(fy == 2022 & (fund_name == "STATE CURE" | fund_name == "LOCAL CURE")) %>% group_by(fund_name, agency_name) %>% summarize(sum=sum(expenditure)) %>% arrange(-sum)exp_temp %>% filter(fy == 2022 & (fund_name == "STATE CURE" | fund_name == "LOCAL CURE")) %>% group_by(agency_name) %>% summarize(sum=sum(expenditure)) %>% arrange(-sum)exp_temp %>% filter(fy == 2021 & (fund_name == "STATE CURE" | fund_name == "LOCAL CURE")) %>% group_by(wh_approp_name, fund_name) %>% summarize(sum=sum(expenditure)) %>% arrange(-sum)Transfers in and Out:
I don’t have much faith in the transfers in and out steps- AWM
I am currently choosing to exclude the totals from this step. Overall, this decreases the total revenues in “All Other Revenues” by a few million dollars.
- in_from_out <- c(“0847”, “0867”, “1175”, “1176”, “1177”, “1178”,
“1181”, “1182”, “1582”, “1592”, “1745”, “1982”, “2174”, “2264”)
- See the methodology document for the list of what these sources/funds are.
rev_temp <- rev_temp %>%
filter(in_ff == 1) %>%
mutate(local = ifelse(is.na(local), 0, local)) %>% # drops all revenue observations that were coded as "local == 1"
filter(local != 1)
# 1175 doesnt exist?
in_from_out <- c("0847", "0867", "1175", "1176", "1177", "1178", "1181", "1182", "1582", "1592", "1745", "1982", "2174", "2264")
# what does this actually include:
# all are items with rev_type = 75 originally.
in_out_df <- rev_temp %>%
mutate(infromout = ifelse(source %in% in_from_out, 1, 0)) %>%
filter(infromout == 1)
rev_temp <- rev_temp %>%
mutate(rev_type_new = ifelse(source %in% in_from_out, "76", rev_type))
# if source contains any of the codes in in_from_out, code them as 76 (all other rev).# revenue types to drop
drop_type <- c("32", "45", "51",
"66", "72", "75", "79", "98")
# drops Blank, Student Fees, Retirement contributions, proceeds/investments,
# bond issue proceeds, interagency receipts, cook IGT, Prior year refunds.
rev_temp <- rev_temp %>% filter(!rev_type_new %in% drop_type)
# keep observations that do not have a revenue type mentioned in drop_type
table(rev_temp$rev_type_new)##
## 02 03 06 09 12 15 18 21 24 27 30 31 33
## 161 124 828 127 575 258 45 1420 450 76 659 124 130
## 35 36 39 42 48 54 57 58 59 60 63 76 78
## 660 5152 9044 2755 31 1239 6451 620 226 103 5081 154 11261
## 99
## 964rev_temp %>%
group_by(fy, rev_type_new) %>%
summarize(total_reciepts = sum(receipts)/1000000) %>%
pivot_wider(names_from = rev_type_new, values_from = total_reciepts, names_prefix = "rev_") # combines smallest 4 categories to to "Other"
# they were the 4 smallest in past years, are they still the 4 smallest?
rev_temp <- rev_temp %>%
mutate(rev_type_new = ifelse(rev_type=="30" | rev_type=="60" | rev_type=="63" | rev_type=="76", "78", rev_type_new))
#table(rev_temp$rev_type_new) # check work
rm(rev_1998_2022)
rm(exp_1998_2022)Pivoting and Merging
- Local Government Transfers (exp_970) should be on the expenditure side
Revenues
I chose to drop rev_76 for Transfers in and Out because I do not
understand why that step occurs in the previously used Stata code. If I
keep rev_76 in and include it in rev_78 for All Other Revenues, then the
difference between R and Stata code should be resolved.
- after Stata code is edited to drop employee insurance premium revenue
from all other revenues. Currently it keeps it in AND subtracts it from
state healthcare expenditures.
ff_rev <- rev_temp %>%
group_by(rev_type_new, fy) %>%
summarize(sum_receipts = sum(receipts, na.rm=TRUE)/1000000 ) %>%
pivot_wider(names_from = "rev_type_new", values_from = "sum_receipts", names_prefix = "rev_")
ff_rev<- left_join(ff_rev, tax_refund)
#ff_rev <- left_join(ff_rev, pension2_fy22, by=c("fy" = "year"))
#ff_rev <- left_join(ff_rev, eehc2_amt)
ff_rev <- mutate_all(ff_rev, ~replace_na(.,0))
ff_rev <- ff_rev %>%
mutate(rev_02 = rev_02 - ref_02,
rev_03 = rev_03 - ref_03,
rev_06 = rev_06 - ref_06,
rev_09 = rev_09 - ref_09,
rev_21 = rev_21 - ref_21,
rev_24 = rev_24 - ref_24,
rev_35 = rev_35 - ref_35
# rev_78new = rev_78 #+ pension_amt #+ eehc
) %>%
select(-c(ref_02:ref_35, rev_99, rev_NA, rev_76#, pension_amt , rev_76,
# , eehc
))
ff_revSince I already pivot_wider()ed the table in the previous code chunk, I now change each column’s name by using rename() to set new variable names. Ideally the final dataframe would have both the variable name and the variable label but I have not done that yet.
aggregate_rev_labels <- ff_rev %>%
rename("INDIVIDUAL INCOME TAXES, gross of local, net of refunds" = rev_02,
"CORPORATE INCOME TAXES, gross of PPRT, net of refunds" = rev_03,
"SALES TAXES, gross of local share" = rev_06 ,
"MOTOR FUEL TAX, gross of local share, net of refunds" = rev_09 ,
"PUBLIC UTILITY TAXES, gross of PPRT" = rev_12,
"CIGARETTE TAXES" = rev_15 ,
"LIQUOR GALLONAGE TAXES" = rev_18,
"INHERITANCE TAX" = rev_21,
"INSURANCE TAXES&FEES&LICENSES, net of refunds" = rev_24 ,
"CORP FRANCHISE TAXES & FEES" = rev_27,
# "HORSE RACING TAXES & FEES" = rev_30, # in Other
"MEDICAL PROVIDER ASSESSMENTS" = rev_31 ,
# "GARNISHMENT-LEVIES " = rev_32 , # dropped
"LOTTERY RECEIPTS" = rev_33 ,
"OTHER TAXES" = rev_35,
"RECEIPTS FROM REVENUE PRODUCNG" = rev_36,
"LICENSES, FEES & REGISTRATIONS" = rev_39 ,
"MOTOR VEHICLE AND OPERATORS" = rev_42 ,
# "STUDENT FEES-UNIVERSITIES" = rev_45, # dropped
"RIVERBOAT WAGERING TAXES" = rev_48 ,
# "RETIREMENT CONTRIBUTIONS " = rev_51, # dropped
"GIFTS AND BEQUESTS" = rev_54,
"FEDERAL OTHER" = rev_57 ,
"FEDERAL MEDICAID" = rev_58,
"FEDERAL TRANSPORTATION" = rev_59 ,
#"OTHER GRANTS AND CONTRACTS" = rev_60, #other
# "INVESTMENT INCOME" = rev_63, # other
# "PROCEEDS,INVESTMENT MATURITIES" = rev_66 , #dropped
# "BOND ISSUE PROCEEDS" = rev_72, #dropped
# "INTER-AGENCY RECEIPTS" = rev_75, #dropped
# "TRANSFER IN FROM OUT FUNDS" = rev_76, #other
"ALL OTHER SOURCES" = rev_78,
# "COOK COUNTY IGT" = rev_79, #dropped
# "PRIOR YEAR REFUNDS" = rev_98 #dropped
)
aggregate_rev_labels# Still contains columns that should be dropped for the clean final aggregate table. Drop the variables I don't want in the output table in the "graphs" section. Expenditures
Create state employee healthcare costs that reflects the health costs minus the optional insurance premiums that came in (\(904\_new = 904 - med\_option\_amt\_recent\)).
Create exp_970 for all local government transfers (exp_971 + exp_972 + exp_975 + exp_976).
ff_exp <- exp_temp %>%
group_by(fy, group) %>%
summarize(sum_expenditures = sum(expenditure, na.rm=TRUE)/1000000 ) %>%
pivot_wider(names_from = "group", values_from = "sum_expenditures", names_prefix = "exp_")%>%
left_join(debt_keep_yearly) %>%
mutate(exp_903 = debt_cost) %>%
# left_join(healthcare_costs_yearly) %>%
# join state employee healthcare and subtract employee premiums
# left_join(emp_premium, by = c("fy" = "year")) %>%
# mutate(exp_904_new = (`healthcare_cost` - `employee_premiums_sum`)) %>% # state employee healthcare premiums
# left_join(retirement_contributions) %>%
# mutate(exp_901_new = exp_901 - contributions/1000000) %>% #employee pension contributions
# join local transfers and create exp_970
left_join(transfers) %>%
mutate(exp_970 = exp_971 + exp_972 + exp_975 + exp_976)
ff_exp<- ff_exp %>% select(-c(debt_cost, exp_971:exp_976)) # drop unwanted columns
ff_expClean Table Outputs
Create total revenues and total expenditures only:
- after aggregating expenditures and revenues, pivoting wider, and
left_joining the additional mini dataframes (med_option_recent,
pension2_fy22, eehc2_amt), then I want to drop the columns that I no
longer want and then pivot_longer(). After pivoting_longer() and
creating
rev_longandexp_long, expenditures and revenues are in the same format and can be combined together for the totals and gap each year.
rev_long <- pivot_longer(ff_rev, rev_02:rev_78, names_to = c("type","Category"), values_to = "Dollars", names_sep = "_") %>%
rename(Year = fy) %>%
mutate(Category_name = case_when(
Category == "02" ~ "INDIVIDUAL INCOME TAXES" ,
Category == "03" ~ "CORPORATE INCOME TAXES" ,
Category == "06" ~ "SALES TAXES" ,
Category == "09" ~ "MOTOR FUEL TAX" ,
Category == "12" ~ "PUBLIC UTILITY TAXES" ,
Category == "15" ~ "CIGARETTE TAXES" ,
Category == "18" ~ "LIQUOR GALLONAGE TAXES" ,
Category == "21" ~ "INHERITANCE TAX" ,
Category == "24" ~ "INSURANCE TAXES&FEES&LICENSES" ,
Category == "27" ~ "CORP FRANCHISE TAXES & FEES" ,
Category == "30" ~ "HORSE RACING TAXES & FEES", # in Other
Category == "31" ~ "MEDICAL PROVIDER ASSESSMENTS" ,
Category == "32" ~ "GARNISHMENT-LEVIES" , # dropped
Category == "33" ~ "LOTTERY RECEIPTS" ,
Category == "35" ~ "OTHER TAXES" ,
Category == "36" ~ "RECEIPTS FROM REVENUE PRODUCING",
Category == "39" ~ "LICENSES, FEES & REGISTRATIONS" ,
Category == "42" ~ "MOTOR VEHICLE AND OPERATORS" ,
Category == "45" ~ "STUDENT FEES-UNIVERSITIES", # dropped
Category == "48" ~ "RIVERBOAT WAGERING TAXES" ,
Category == "51" ~ "RETIREMENT CONTRIBUTIONS" , # dropped
Category == "54" ~ "GIFTS AND BEQUESTS",
Category == "57" ~ "FEDERAL OTHER" ,
Category == "58" ~ "FEDERAL MEDICAID",
Category == "59" ~ "FEDERAL TRANSPORTATION" ,
Category == "60" ~ "OTHER GRANTS AND CONTRACTS", #other
Category == "63" ~ "INVESTMENT INCOME", # other
Category == "66" ~ "PROCEEDS,INVESTMENT MATURITIES" , #dropped
Category == "72" ~ "BOND ISSUE PROCEEDS", #dropped
Category == "75" ~ "INTER-AGENCY RECEIPTS ", #dropped
Category == "76" ~ "TRANSFER IN FROM OUT FUNDS", #other
Category == "78" ~ "ALL OTHER SOURCES" ,
Category == "79" ~ "COOK COUNTY IGT", #dropped
Category == "98" ~ "PRIOR YEAR REFUNDS", #dropped
T ~ "Check Me!"
) )%>%
mutate(Category_name = str_to_title(Category_name))
exp_long <- pivot_longer(ff_exp, exp_402:exp_970 , names_to = c("type", "Category"), values_to = "Dollars", names_sep = "_") %>%
rename(Year = fy ) %>%
mutate(Category_name =
case_when(
Category == "402" ~ "AGING" ,
Category == "406" ~ "AGRICULTURE",
Category == "416" ~ "CENTRAL MANAGEMENT",
Category == "418" ~ "CHILDREN AND FAMILY SERVICES",
Category == "420" ~ "COMMERCE AND ECONOMIC OPPORTUNITY",
Category == "422" ~ "NATURAL RESOURCES" ,
Category == "426" ~ "CORRECTIONS",
Category == "427" ~ "EMPLOYMENT SECURITY" ,
Category == "444" ~ "HUMAN SERVICES" ,
Category == "448" ~ "Innovation and Technology", # AWM added fy2022
Category == "478" ~ "FAMILY SERVICES net Medicaid",
Category == "482" ~ "PUBLIC HEALTH",
Category == "492" ~ "REVENUE",
Category == "494" ~ "TRANSPORTATION" ,
Category == "532" ~ "ENVIRONMENTAL PROTECT AGENCY" ,
Category == "557" ~ "IL STATE TOLL HIGHWAY AUTH" ,
Category == "684" ~ "IL COMMUNITY COLLEGE BOARD",
Category == "691" ~ "IL STUDENT ASSISTANCE COMM" ,
Category == "900" ~ "NOT IN FRAME",
Category == "901" ~ "STATE PENSION CONTRIBUTION",
Category == "903" ~ "DEBT SERVICE",
Category == "904" ~ "State Employee Healthcare",
Category == "910" ~ "LEGISLATIVE" ,
Category == "920" ~ "JUDICIAL" ,
Category == "930" ~ "ELECTED OFFICERS" ,
Category == "940" ~ "OTHER HEALTH-RELATED",
Category == "941" ~ "PUBLIC SAFETY" ,
Category == "942" ~ "ECON DEVT & INFRASTRUCTURE" ,
Category == "943" ~ "CENTRAL SERVICES",
Category == "944" ~ "BUS & PROFESSION REGULATION" ,
Category == "945" ~ "MEDICAID" ,
Category == "946" ~ "CAPITAL IMPROVEMENT" ,
Category == "948" ~ "OTHER DEPARTMENTS" ,
Category == "949" ~ "OTHER BOARDS & COMMISSIONS" ,
Category == "959" ~ "K-12 EDUCATION" ,
Category == "960" ~ "UNIVERSITY EDUCATION",
Category == "970" ~ "Local Govt Transfers",
T ~ "CHECK ME!")
) %>%
mutate(Category_name = str_to_title(Category_name))
#write_csv(exp_long, "expenditures_recoded_long_FY22.csv")
#write_csv(rev_long, "revenue_recoded_long_FY22.csv")
aggregated_totals_long <- rbind(rev_long, exp_long)
aggregated_totals_longyear_totals <- aggregated_totals_long %>%
group_by(type, Year) %>%
summarize(Dollars = sum(Dollars, na.rm = TRUE)) %>%
pivot_wider(names_from = "type", values_from = Dollars) %>%
rename(
Expenditures = exp,
Revenue = rev) %>%
mutate(Gap = Revenue - Expenditures) %>%
arrange(desc(Year))
# creates variable for the Gap each year
year_totals#write_csv(aggregated_totals_long, "aggregated_totals.csv")Graphs
Graphs made from aggregated_totals_long dataframe.
year_totals %>%
ggplot() +
# geom_smooth adds regression line, graphed first so it appears behind line graph
geom_smooth(aes(x = Year, y = Revenue), color = "light green", method = "lm", se = FALSE) +
geom_smooth(aes(x = Year, y = Expenditures), color = "gray", method = "lm", se = FALSE) +
# line graph of revenue and expenditures
geom_line(aes(x = Year, y = Revenue), color = "green4") +
geom_line(aes(x = Year, y = Expenditures), color = "black") +
# labels
theme_bw() +
scale_y_continuous(labels = comma)+
xlab("Year") +
ylab("Millions of Dollars") +
ggtitle("Illinois Expenditures and Revenue Totals, 1998-2022")
Expenditure and revenue amounts in millions of dollars:
exp_long %>%
filter(Year == 2022) %>%
#mutate(`Total Expenditures`= sum(Dollars, na.rm = TRUE)) %>%
# select(-c(Year, `Total Expenditures`)) %>%
arrange(desc(`Dollars`)) %>%
ggplot() +
geom_col(aes(x = fct_reorder(Category_name, `Dollars`), y = `Dollars`))+
coord_flip() +
theme_bw() +
labs(title = "Expenditures for FY2022") +
xlab("Expenditure Categories") +
ylab("Millions of Dollars") 
rev_long %>%
filter(Year == 2022) %>%
#mutate(`Total Expenditures`= sum(Dollars, na.rm = TRUE)) %>%
# select(-c(Year, `Total Expenditures`)) %>%
arrange(desc(`Dollars`)) %>%
ggplot() +
geom_col(aes(x = fct_reorder(Category_name, `Dollars`), y = `Dollars`))+
coord_flip() +
theme_bw() +
labs(title = "Revenues for FY2022")+
xlab("Revenue Categories") +
ylab("Millions of Dollars") 
Expenditure and revenues when focusing on largest categories and combining others into “All Other Expenditures(Revenues)”:
exp_long %>%
filter( Year == 2022) %>%
mutate(rank = rank(Dollars),
Category_name = ifelse(rank > 13, Category_name, 'All Other Expenditures')) %>%
# select(-c(Year, Dollars, rank)) %>%
arrange(desc(Dollars)) %>%
ggplot() +
geom_col(aes(x = fct_reorder(Category_name, `Dollars`), y = `Dollars`), fill = "light green")+
coord_flip() +
theme_bw() +
labs(title = "Expenditures for FY2022") +
xlab("") +
ylab("Millions of Dollars")
rev_long %>%
filter( Year == 2022) %>%
mutate(rank = rank(Dollars),
Category_name = ifelse(rank > 10, Category_name, 'All Other Sources')) %>%
arrange(desc(Dollars)) %>%
ggplot() +
geom_col(aes(x = fct_reorder(Category_name, `Dollars`), y = `Dollars`), fill = "light blue")+
coord_flip() +
theme_bw() +
labs(title = "Revenues for FY2022") +
xlab("") +
ylab("Millions of Dollars")
Changes in Categories - 2021 to 2022 Dot Plot Attempt:
rev_long %>%
filter(Year == "2022" | Year == "2021") %>%
mutate(Year = as.character(Year)) %>%
ggplot(aes(x = Dollars, y = reorder(Category, Dollars))) +
geom_line(aes(group = Category) )+
geom_text(aes(x = ifelse(Year == "2022", as.numeric(Dollars), NA), label = ifelse(Year == "2022", Category_name, "")),
hjust = -0.2,
size = 2.8) +
geom_point(aes(color = Year), size=2) +
labs(title = "2021 to 2022 Change in Revenue", x = "Millions of Dollars" , y = "", caption = "") +
scale_color_brewer(palette = "paired", labels = c("FY 2021", "FY 2022"))+
theme_classic()+
theme(
legend.position = "bottom" ,
axis.text.y = element_blank(),
axis.ticks.y = element_blank(),
axis.line.y.left = element_blank(),
axis.line.x = element_blank(),
axis.title.y = element_blank(),
axis.ticks.x = element_blank())+
scale_x_continuous(limits = c(0, 30000))
exp_long %>%
filter(Year == "2022" | Year == "2021") %>%
mutate(Year = as.character(Year)) %>%
ggplot(aes(x = Dollars, y = reorder(Category, Dollars))) +
geom_line(aes(group = Category) )+
geom_text(aes(x = ifelse(Year == "2022", as.numeric(Dollars), NA), label = ifelse(Year == "2022", Category_name, "")),
hjust = -0.2,
size = 2.8) +
geom_point(aes(color = Year), size=2) +
labs(title = "2021 to 2022 Change in Expenditures", x = "Millions of Dollars" , y = "", caption = "") +
scale_color_brewer(palette = "paired", labels = c("FY 2021", "FY 2022"))+
theme_classic()+
theme(
legend.position = "bottom" ,
axis.text.y = element_blank(),
axis.ticks.y = element_blank(),
axis.line.y.left = element_blank(),
axis.line.x = element_blank(),
axis.title.y = element_blank(),
axis.ticks.x = element_blank())+
scale_x_continuous(limits = c(0, 30000))
CAGR / Growth
Each year, you will need to update the CAGR formulas!
calc_cagr is a function created for calculating the
CAGRs for different spans of time.
# function for calculating the CAGR
calc_cagr <- function(df, n) {
df <- exp_long %>%
select(-type) %>%
arrange(Category_name, Year) %>%
group_by(Category_name) %>%
mutate(cagr = ((`Dollars` / lag(`Dollars`, n)) ^ (1 / n)) - 1)
return(df)
}
# This works for one variable at a time
cagr_24 <- calc_cagr(exp_long, 24) %>%
# group_by(Category) %>%
summarize(cagr_24 = round(sum(cagr*100, na.rm = TRUE), 2))
cagr23_precovid <- exp_long %>%
filter(Year <= 2019) %>%
calc_cagr(21) %>%
summarize(cagr_21 = round(sum(cagr*100, na.rm = TRUE), 2))
cagr_10 <- calc_cagr(exp_long, 10) %>%
filter(Year == 2022) %>%
summarize(cagr_10 = case_when(Year == 2022 ~ round(sum(cagr*100, na.rm = TRUE), 2)))
cagr_5 <- calc_cagr(exp_long, 5) %>%
filter(Year == 2022) %>%
summarize(cagr_5 = case_when(Year == 2022 ~ round(sum(cagr*100, na.rm = TRUE), 2)))
cagr_3 <- calc_cagr(exp_long, 3) %>%
filter(Year == 2022) %>%
summarize(cagr_3 = case_when(Year == 2022 ~ round(sum(cagr*100, na.rm = TRUE), 2)))
cagr_2 <- calc_cagr(exp_long, 2) %>%
filter(Year == 2022) %>%
summarize(cagr_2 = case_when(Year == 2022 ~ round(sum(cagr*100, na.rm = TRUE), 2)))
cagr_1 <- calc_cagr(exp_long, 1) %>%
filter(Year == 2022) %>%
summarize(cagr_1 = case_when(Year == 2022 ~ round(sum(cagr*100, na.rm = TRUE), 2)))
CAGR_expenditures_summary <- data.frame(cagr_1, cagr_2, cagr_3, cagr_5, cagr_10, cagr_24 ) %>%
select(-c(Category_name.1, Category_name.2, Category_name.3, Category_name.4, Category_name.5 )) %>%
rename("Expenditure Category" = Category_name, "1 Year CAGR" = cagr_1, "2 Year CAGR" = cagr_2, "3 Year CAGR" = cagr_3, "5 Year CAGR" = cagr_5, "10 Year CAGR" = cagr_10,"24 Year CAGR" = cagr_24 )
CAGR_expenditures_summary %>%
kbl(caption = "CAGR Calculations for Expenditure Categories") %>%
kable_styling(bootstrap_options = c("striped"))| Expenditure Category | 1 Year CAGR | 2 Year CAGR | 3 Year CAGR | 5 Year CAGR | 10 Year CAGR | 24 Year CAGR |
|---|---|---|---|---|---|---|
| Aging | 2.69 | 5.01 | 5.94 | -1.34 | 3.97 | 7.33 |
| Agriculture | 32.63 | 11.30 | 5.42 | 4.93 | 2.48 | 0.87 |
| Bus & Profession Regulation | 7.97 | 5.63 | 3.16 | 1.67 | -1.69 | 1.42 |
| Capital Improvement | -6.58 | 17.24 | 18.10 | 10.64 | -3.63 | 2.15 |
| Central Management | -2.75 | -1.35 | 6.80 | 0.21 | 4.20 | 4.25 |
| Children And Family Services | -3.90 | 0.55 | 2.79 | 3.08 | 0.51 | -0.16 |
| Commerce And Economic Opportunity | -25.67 | 50.78 | 34.76 | 16.78 | 3.22 | 4.73 |
| Corrections | -6.86 | -1.19 | -1.73 | 3.32 | 1.60 | 1.77 |
| Debt Service | -0.83 | 1.59 | -0.70 | 1.65 | 1.19 | 6.11 |
| Elected Officers | 3.50 | 5.26 | 2.22 | 5.99 | 3.90 | 3.72 |
| Employment Security | -16.09 | 7.77 | 7.46 | 7.21 | 0.16 | 1.75 |
| Environmental Protect Agency | -3.16 | -4.67 | -8.10 | -6.72 | 0.00 | 3.15 |
| Family Services Net Medicaid | -4.06 | 3.65 | -8.82 | -0.60 | -3.55 | 5.14 |
| Human Services | 11.09 | 10.16 | 8.97 | 6.11 | 3.28 | 2.60 |
| Il State Toll Highway Auth | 7.06 | 4.69 | 6.28 | 3.57 | 11.64 | 7.54 |
| Judicial | -2.37 | 3.00 | 6.81 | 3.75 | 2.73 | 2.71 |
| K-12 Education | 9.87 | 8.79 | 7.94 | 6.51 | 4.10 | 4.12 |
| Legislative | 19.67 | 11.90 | 10.76 | 7.37 | 2.38 | 3.19 |
| Local Govt Transfers | 44.14 | 26.60 | 16.64 | 9.88 | 6.39 | 4.65 |
| Medicaid | 9.04 | 13.38 | 14.62 | 9.92 | 8.89 | 7.20 |
| Natural Resources | 0.26 | 2.38 | 0.99 | 4.64 | 2.48 | 1.61 |
| Other Boards & Commissions | -1.02 | 7.90 | 2.33 | 2.39 | -2.93 | 4.06 |
| Other Departments | 1.58 | 4.66 | 8.10 | 5.56 | 7.03 | 9.08 |
| Public Health | -11.17 | 22.29 | 24.19 | 17.54 | 7.44 | 7.11 |
| Public Safety | -14.28 | 7.54 | 19.34 | 15.80 | 8.06 | 5.88 |
| Revenue | 31.40 | 39.94 | 54.41 | 35.09 | 15.96 | 7.15 |
| State Employee Healthcare | 4.30 | 0.39 | -1.58 | -1.98 | 2.47 | 6.07 |
| State Pension Contribution | 15.42 | 10.80 | 9.67 | 9.26 | 9.38 | 10.76 |
| Transportation | -19.00 | 2.93 | 7.84 | 0.69 | -0.32 | 3.31 |
| University Education | 2.70 | 1.44 | 3.24 | -1.10 | -0.96 | 0.35 |
# to have it as a csv, uncomment the line below
#write_csv(CAGR_expenditures_summary, "CAGR_expenditures_summary.csv")calc_cagr <- function(df, n) {
df <- rev_long %>%
arrange(Category_name, Year) %>%
group_by(Category_name) %>%
mutate(cagr = ((Dollars / lag(Dollars, n)) ^ (1 / n)) - 1)
return(df)
}
# This works for one variable at a time
cagr_24 <- calc_cagr(rev_long, 24) %>%
# group_by(Category) %>%
summarize(cagr_24 = round(sum(cagr*100, na.rm = TRUE), 2))
cagr_10 <- calc_cagr(rev_long, 10) %>%
filter(Year == 2022) %>%
summarize(cagr_10 = case_when(Year == 2022 ~ round(sum(cagr*100, na.rm = TRUE), 2)))
cagr_5 <- calc_cagr(rev_long, 5) %>%
filter(Year == 2022) %>%
summarize(cagr_5 = case_when(Year == 2022 ~ round(sum(cagr*100, na.rm = TRUE), 2)))
cagr_3 <- calc_cagr(rev_long, 3) %>%
filter(Year == 2022) %>%
summarize(cagr_3 = case_when(Year == 2022 ~ round(sum(cagr*100, na.rm = TRUE), 2)))
cagr_2 <- calc_cagr(rev_long, 2) %>%
filter(Year == 2022) %>%
summarize(cagr_2 = case_when(Year == 2022 ~ round(sum(cagr*100, na.rm = TRUE), 2)))
cagr_1 <- calc_cagr(rev_long, 1) %>%
filter(Year == 2022) %>%
summarize(cagr_1 = case_when(Year == 2022 ~ round(sum(cagr*100, na.rm = TRUE), 2)))
CAGR_revenue_summary <- data.frame(cagr_1, cagr_2, cagr_3, cagr_5, cagr_10, cagr_24) %>%
select(-c(Category_name.1, Category_name.2, Category_name.3, Category_name.4, Category_name.5 )) %>%
rename("Revenue Category" = Category_name, "1 Year CAGR" = cagr_1, "2 Year CAGR" = cagr_2, "3 Year CAGR" = cagr_3, "5 Year CAGR" = cagr_5, "10 Year CAGR" = cagr_10,"24 Year CAGR" = cagr_24 )
CAGR_revenue_summary %>%
kbl(caption = "CAGR Calculations for Revenue Sources") %>%
kable_styling(bootstrap_options = c("striped"))| Revenue Category | 1 Year CAGR | 2 Year CAGR | 3 Year CAGR | 5 Year CAGR | 10 Year CAGR | 24 Year CAGR |
|---|---|---|---|---|---|---|
| All Other Sources | 37.70 | 12.92 | 13.64 | 8.08 | 6.28 | 4.54 |
| Cigarette Taxes | -8.25 | -0.54 | 3.02 | 1.49 | 3.33 | 2.51 |
| Corp Franchise Taxes & Fees | -32.40 | 1.21 | -4.37 | 0.85 | 1.18 | 2.55 |
| Corporate Income Taxes | 76.66 | 72.77 | 38.19 | 32.31 | 13.59 | 7.70 |
| Federal Medicaid | 8.48 | 17.30 | 16.43 | 12.76 | 11.30 | 7.52 |
| Federal Other | 114.47 | 42.66 | 49.24 | 27.19 | 11.91 | 7.17 |
| Federal Transportation | -22.95 | 1.39 | 10.40 | -2.73 | -0.06 | 3.33 |
| Gifts And Bequests | 23.76 | 42.11 | 18.49 | 10.46 | 10.65 | 11.43 |
| Individual Income Taxes | 12.60 | 16.35 | 9.25 | 15.22 | 5.36 | 5.68 |
| Inheritance Tax | 35.98 | 48.20 | 16.36 | 18.47 | 10.12 | 3.74 |
| Insurance Taxes&Fees&Licenses | -3.42 | 12.76 | 5.20 | 2.79 | 3.20 | 6.56 |
| Licenses, Fees & Registrations | -4.68 | 15.06 | 16.83 | 9.26 | 6.23 | 7.87 |
| Liquor Gallonage Taxes | 2.53 | 2.81 | 2.49 | 1.69 | 1.37 | 7.45 |
| Lottery Receipts | -6.17 | 9.62 | 1.63 | 2.27 | 0.90 | 2.15 |
| Medical Provider Assessments | -1.98 | 3.67 | 16.26 | 11.80 | 8.33 | 8.36 |
| Motor Fuel Tax | 6.12 | 4.36 | 23.16 | 13.42 | 6.98 | 2.78 |
| Motor Vehicle And Operators | -5.59 | 4.66 | -0.04 | 0.15 | 0.64 | 3.21 |
| Other Taxes | 63.89 | 32.74 | 17.36 | 13.92 | 17.13 | 7.87 |
| Public Utility Taxes | 3.09 | -0.43 | -1.43 | 0.22 | -0.48 | 0.70 |
| Receipts From Revenue Producing | 3.01 | 4.78 | -2.68 | 1.45 | 3.49 | 5.07 |
| Riverboat Wagering Taxes | 80.77 | -1.03 | -8.90 | -6.18 | -4.20 | 1.75 |
| Sales Taxes | 11.29 | 12.22 | 7.40 | 6.27 | 4.43 | 3.23 |
# to have it as a csv, uncomment the line below
#write_csv(CAGR_revenue_summary, "CAGR_revenue_summary.csv")
rm(cagr_1, cagr_2, cagr_3, cagr_5, cagr_10, cagr_24)Expenditure and Revenue Growth using a lag formula:
exp_long %>%
group_by(Category_name) %>%
mutate(Growth = ((Dollars) - lag(Dollars))/lag(Dollars) *100) %>%
summarize(Growth = round(mean(Growth, na.rm = TRUE), 2)) rev_long %>%
group_by(Category_name) %>%
mutate(Growth = ((Dollars) - lag(Dollars))/lag(Dollars) *100) %>%
summarize(Growth = round(mean(Growth, na.rm = TRUE), 2))Change from Previous Year
revenue_change <- rev_long %>%
select(-c(type,Category)) %>%
filter(Year > 2020) %>%
pivot_wider(names_from = Year , values_from = Dollars, names_prefix = "Dollars_") %>%
mutate(
"FY 2022 Revenues ($ billions)" = round(Dollars_2022/1000, digits = 1),
# "Change from 2021 to 2022" = round(Dollars_2022 - Dollars_2021, digits = 2),
"Percent Change from 2021 to 2022" = round(((Dollars_2022 -Dollars_2021)/Dollars_2021*100), digits = 2)) %>%
left_join(CAGR_revenue_summary, by = c("Category_name" = "Revenue Category")) %>%
arrange(-`FY 2022 Revenues ($ billions)`)%>%
#select(-c(Dollars_2021, Dollars_2021, `1 Year CAGR`:`10 Year CAGR`)) %>%
rename( "Compound Annual Growth, 1998-2022*" = `24 Year CAGR`,
"FY2022 Revenue Category" = Category_name ) %>%
select(-c(Dollars_2021, Dollars_2022, `1 Year CAGR`:`10 Year CAGR`))
revenue_change %>%
kbl(caption = "Yearly Change in Revenue") %>%
kable_styling(bootstrap_options = c("striped"))| FY2022 Revenue Category | FY 2022 Revenues ($ billions) | Percent Change from 2021 to 2022 | Compound Annual Growth, 1998-2022* |
|---|---|---|---|
| Individual Income Taxes | 23.8 | 12.60 | 5.68 |
| Federal Other | 19.8 | 114.47 | 7.17 |
| Federal Medicaid | 19.0 | 8.48 | 7.52 |
| Sales Taxes | 15.4 | 11.29 | 3.23 |
| Corporate Income Taxes | 9.7 | 76.66 | 7.70 |
| Medical Provider Assessments | 3.7 | -1.98 | 8.36 |
| All Other Sources | 2.7 | 37.70 | 4.54 |
| Motor Fuel Tax | 2.5 | 6.12 | 2.78 |
| Receipts From Revenue Producing | 2.4 | 3.01 | 5.07 |
| Licenses, Fees & Registrations | 1.9 | -4.68 | 7.87 |
| Gifts And Bequests | 1.9 | 23.76 | 11.43 |
| Federal Transportation | 1.8 | -22.95 | 3.33 |
| Motor Vehicle And Operators | 1.6 | -5.59 | 3.21 |
| Public Utility Taxes | 1.4 | 3.09 | 0.70 |
| Lottery Receipts | 1.4 | -6.17 | 2.15 |
| Other Taxes | 1.4 | 63.89 | 7.87 |
| Cigarette Taxes | 0.8 | -8.25 | 2.51 |
| Inheritance Tax | 0.6 | 35.98 | 3.74 |
| Insurance Taxes&Fees&Licenses | 0.6 | -3.42 | 6.56 |
| Liquor Gallonage Taxes | 0.3 | 2.53 | 7.45 |
| Riverboat Wagering Taxes | 0.3 | 80.77 | 1.75 |
| Corp Franchise Taxes & Fees | 0.2 | -32.40 | 2.55 |
expenditure_change <- exp_long %>%
select(-c(type,Category)) %>%
filter(Year > 2020) %>%
pivot_wider(names_from = Year , values_from = Dollars, names_prefix = "Dollars_") %>%
mutate("FY 2022 Expenditures ($ billions)" = round(Dollars_2022/1000, digits = 1),
# "Change from 2021 to 2022" = Dollars_2022 - Dollars_2021,
"Percent Change from 2021 to 2022" = round((Dollars_2022 -Dollars_2021)/Dollars_2021*100, digits = 2) )%>%
left_join(CAGR_expenditures_summary, by = c("Category_name" = "Expenditure Category")) %>%
arrange(-`FY 2022 Expenditures ($ billions)`)%>%
select(-c(Dollars_2022, Dollars_2021, `1 Year CAGR`:`10 Year CAGR`)) %>%
rename( "Compound Annual Growth, 1998-2022*" = `24 Year CAGR`,
"FY2022 Expenditure Category" = Category_name )
expenditure_change %>%
kbl(caption = "Yearly Change in Expenditures") %>%
kable_styling(bootstrap_options = c("striped"))| FY2022 Expenditure Category | FY 2022 Expenditures ($ billions) | Percent Change from 2021 to 2022 | Compound Annual Growth, 1998-2022* |
|---|---|---|---|
| Medicaid | 28.7 | 9.04 | 7.20 |
| K-12 Education | 13.4 | 9.87 | 4.12 |
| Local Govt Transfers | 10.3 | 44.14 | 4.65 |
| Human Services | 7.3 | 11.09 | 2.60 |
| State Pension Contribution | 6.5 | 15.42 | 10.76 |
| Other Departments | 4.9 | 1.58 | 9.08 |
| Transportation | 4.3 | -19.00 | 3.31 |
| State Employee Healthcare | 3.0 | 4.30 | 6.07 |
| University Education | 2.3 | 2.70 | 0.35 |
| Revenue | 2.2 | 31.40 | 7.15 |
| Il State Toll Highway Auth | 2.1 | 7.06 | 7.54 |
| Debt Service | 2.0 | -0.83 | 6.11 |
| Public Safety | 1.7 | -14.28 | 5.88 |
| Corrections | 1.5 | -6.86 | 1.77 |
| Commerce And Economic Opportunity | 1.4 | -25.67 | 4.73 |
| Children And Family Services | 1.3 | -3.90 | -0.16 |
| Aging | 1.2 | 2.69 | 7.33 |
| Central Management | 1.2 | -2.75 | 4.25 |
| Elected Officers | 1.0 | 3.50 | 3.72 |
| Public Health | 0.8 | -11.17 | 7.11 |
| Environmental Protect Agency | 0.7 | -3.16 | 3.15 |
| Judicial | 0.5 | -2.37 | 2.71 |
| Family Services Net Medicaid | 0.4 | -4.06 | 5.14 |
| Capital Improvement | 0.4 | -6.58 | 2.15 |
| Natural Resources | 0.3 | 0.26 | 1.61 |
| Employment Security | 0.3 | -16.09 | 1.75 |
| Bus & Profession Regulation | 0.2 | 7.97 | 1.42 |
| Other Boards & Commissions | 0.2 | -1.02 | 4.06 |
| Agriculture | 0.1 | 32.63 | 0.87 |
| Legislative | 0.1 | 19.67 | 3.19 |
Create summary file
Saves main items in one excel file named
summary_file.xlsx. Delete eval=FALSE to run on
local computer.
#install.packages("openxlsx")
library(openxlsx)
dataset_names <- list('rev_long' = rev_long, 'exp_long' = exp_long,
`Table 1` = expenditure_change, `Table 2` = revenue_change,
'Table 4.a' = CAGR_revenue_summary, 'Table 4.b' = CAGR_expenditures_summary,
'year_totals' = year_totals)
write.xlsx(dataset_names, file = 'summary_file_FY2022.xlsx')Forecasting attempt
#### Revenues
year_totals <- year_totals %>%
arrange(Year)
#ts_rev <- year_totals %>% select(Year, Revenue ) %>% arrange(Year)
tsrev <- ts(year_totals$Revenue, start ="1998", frequency = 1) # yearly data
start(tsrev) # 1998, January## [1] 1998 1end(tsrev) ## 2022 ## [1] 2022 1summary(tsrev)## Min. 1st Qu. Median Mean 3rd Qu. Max.
## 31265 40856 50213 55410 62772 113393plot(tsrev)
abline(reg=lm(tsrev~time(tsrev)))
#### ARIMAs
mymodel <- auto.arima(tsrev, seasonal = FALSE)
mymodel # ARIMA (0, 1, 0) with drift## Series: tsrev
## ARIMA(0,2,0)
##
## sigma^2 = 17113123: log likelihood = -224.17
## AIC=450.34 AICc=450.53 BIC=451.48myforecastrev <- forecast(mymodel, h = 20)
plot(myforecastrev, xlab ="",
ylab ="Total Revenue",
main ="Chicago Revenue")
tsexp <- ts(year_totals$Expenditures, start = "1998", frequency = 1)
model_exp<- auto.arima(tsexp, seasonal = FALSE)
model_exp # ARIMA (0,1,1) with drift## Series: tsexp
## ARIMA(0,1,0) with drift
##
## Coefficients:
## drift
## 2873.2157
## s.e. 850.3206
##
## sigma^2 = 18107502: log likelihood = -234.09
## AIC=472.17 AICc=472.74 BIC=474.53forecast_exp <- forecast(model_exp, h = 20)
plot(forecast_exp, xlab ="",
ylab ="Total Expenditures",
main ="Chicago Expenditures")
p <- forecast(model_exp, h = 20) %>%
autoplot() +
ylab("Dollars (Millions)") +
xlab("Year") +
ggtitle("Forecasted Expenditures") +
theme_classic() +
scale_y_continuous(labels = dollar )
summary(forecast_exp)##
## Forecast method: ARIMA(0,1,0) with drift
##
## Model Information:
## Series: tsexp
## ARIMA(0,1,0) with drift
##
## Coefficients:
## drift
## 2873.2157
## s.e. 850.3206
##
## sigma^2 = 18107502: log likelihood = -234.09
## AIC=472.17 AICc=472.74 BIC=474.53
##
## Error measures:
## ME RMSE MAE MPE MAPE MASE
## Training set 1.133809 4081.532 2607.652 -0.6194021 4.248761 0.6351045
## ACF1
## Training set -0.1743016
##
## Forecasts:
## Point Forecast Lo 80 Hi 80 Lo 95 Hi 95
## 2023 103048.8 97595.47 108502.2 94708.63 111389.1
## 2024 105922.1 98209.83 113634.3 94127.21 117716.9
## 2025 108795.3 99349.76 118240.8 94349.60 123241.0
## 2026 111668.5 100761.74 122575.2 94988.06 128348.9
## 2027 114541.7 102347.59 126735.8 95892.42 133191.0
## 2028 117414.9 104056.94 130772.9 96985.65 137844.2
## 2029 120288.1 105859.87 134716.4 98222.00 142354.3
## 2030 123161.4 107736.88 138585.8 99571.66 146751.1
## 2031 126034.6 109674.45 142394.7 101013.92 151055.2
## 2032 128907.8 111662.70 146152.9 102533.71 155281.9
## 2033 131781.0 113694.21 149867.8 104119.63 159442.4
## 2034 134654.2 115763.17 153545.3 105762.86 163545.6
## 2035 137527.4 117865.01 157189.9 107456.35 167598.5
## 2036 140400.7 119995.99 160805.3 109194.42 171606.9
## 2037 143273.9 122153.04 164394.7 110972.34 175575.4
## 2038 146147.1 124333.58 167960.6 112786.21 179508.0
## 2039 149020.3 126535.46 171505.1 114632.70 183407.9
## 2040 151893.5 128756.80 175030.2 116508.97 187278.1
## 2041 154766.7 130996.02 178537.4 118412.57 191120.9
## 2042 157639.9 133251.71 182028.2 120341.36 194938.5annotation <- data.frame(
x = c(2027, 2032),
y = c(130000, 100000), label = c("$114 ± 19 Billion in 2027","$128 ± 25 Billion in 2032 ")
)
p + geom_label(data = annotation, aes(x=x, y=y, label=label), size = 3) + labs(title = "Forecasted Expenditures", caption = "Projected values at 95% confidence interval.
Dark blue represents 80% liklihood of falling with that range,
light blue represents 95% liklihood of being in projected range.")
#### revenue chart
model_rev <- auto.arima(tsrev, seasonal = FALSE)
forecast_rev <- forecast(model_rev, h = 20)
q <- forecast(forecast_rev, h = 20) %>%
autoplot() +
ylab("Dollars (Millions)") +
xlab("Year") +
ggtitle("Forecasted Revenue") +
theme_classic() +
scale_y_continuous(labels = dollar )
summary(forecast_rev)##
## Forecast method: ARIMA(0,2,0)
##
## Model Information:
## Series: tsrev
## ARIMA(0,2,0)
##
## sigma^2 = 17113123: log likelihood = -224.17
## AIC=450.34 AICc=450.53 BIC=451.48
##
## Error measures:
## ME RMSE MAE MPE MAPE MASE ACF1
## Training set 791.8803 3967.88 2917.327 0.8207641 4.67096 0.7858976 -0.1238544
##
## Forecasts:
## Point Forecast Lo 80 Hi 80 Lo 95 Hi 95
## 2023 134979.2 129677.7 140280.8 126871.3 143087.2
## 2024 156565.8 144711.3 168420.4 138435.8 174695.8
## 2025 178152.4 158315.9 197988.9 147815.1 208489.7
## 2026 199739.0 170701.4 228776.6 155329.8 244148.2
## 2027 221325.6 182008.4 260642.7 161195.2 281456.0
## 2028 242912.2 192338.9 293485.5 165567.0 320257.4
## 2029 264498.8 201770.3 327227.2 168563.8 360433.7
## 2030 286085.4 210364.4 361806.3 170280.2 401890.5
## 2031 307671.9 218171.9 397172.0 170793.5 444550.4
## 2032 329258.5 225235.1 433281.9 170168.5 488348.6
## 2033 350845.1 231590.3 470099.9 168460.6 533229.6
## 2034 372431.7 237268.8 507594.6 165717.9 579145.5
## 2035 394018.3 242298.3 545738.2 161982.7 626053.9
## 2036 415604.9 246703.3 584506.5 157292.2 673917.6
## 2037 437191.5 250505.5 623877.4 151680.0 722702.9
## 2038 458778.1 253724.9 663831.2 145176.3 772379.8
## 2039 480364.6 256379.2 704350.1 137808.4 822920.8
## 2040 501951.2 258484.8 745417.6 129601.5 874300.9
## 2041 523537.8 260056.9 787018.8 120578.5 926497.1
## 2042 545124.4 261109.2 829139.7 110760.6 979488.3annotation <- data.frame(
x = c(2027, 2032),
y = c(130000, 300000),
label = c("Crazy number 1 in 2027","crazy number 2 in 2032")
)
q+ geom_label(data = annotation, aes(x=x, y=y, label=label), size = 3) +
labs(caption = "Skewed by 2022 federal revenues")
revenue forecasting using precovid trends:
# revenue using precovid trends
tsrev <- ts(year_totals$Revenue, start ="1998", end = "2021", frequency = 1) # yearly data
#### revenue chart
model_rev <- auto.arima(tsrev, seasonal = FALSE)
forecast_rev <- forecast(model_rev, h = 23)
c <- forecast(forecast_rev, h = 20) %>%
autoplot() +
ylab("Dollars (Millions)") +
xlab("Year") +
ggtitle("Forecasted Revenue") +
theme_classic() +
scale_y_continuous(labels = dollar )
summary(forecast_rev)##
## Forecast method: ARIMA(0,1,0) with drift
##
## Model Information:
## Series: tsrev
## ARIMA(0,1,0) with drift
##
## Coefficients:
## drift
## 2632.2338
## s.e. 700.2844
##
## sigma^2 = 11791824: log likelihood = -219.38
## AIC=442.76 AICc=443.36 BIC=445.03
##
## Error measures:
## ME RMSE MAE MPE MAPE MASE ACF1
## Training set 1.193018 3287.731 2281.162 -0.7463939 3.941563 0.777242 0.1262769
##
## Forecasts:
## Point Forecast Lo 80 Hi 80 Lo 95 Hi 95
## 2022 94438.29 90037.55 98839.04 87707.93 101168.7
## 2023 97070.53 90846.93 103294.13 87552.35 106588.7
## 2024 99702.76 92080.44 107325.08 88045.43 111360.1
## 2025 102335.00 93533.50 111136.49 88874.27 115795.7
## 2026 104967.23 95126.86 114807.60 89917.68 120016.8
## 2027 107599.46 96819.87 118379.05 91113.50 124085.4
## 2028 110231.70 98588.41 121874.98 92424.83 128038.6
## 2029 112863.93 100416.73 125311.13 93827.59 131900.3
## 2030 115496.16 102293.92 128698.41 95305.07 135687.3
## 2031 118128.40 104212.01 132044.79 96845.12 139411.7
## 2032 120760.63 106165.00 135356.26 98438.54 143082.7
## 2033 123392.87 108148.22 138637.51 100078.20 146707.5
## 2034 126025.10 110157.97 141892.23 101758.43 150291.8
## 2035 128657.33 112191.24 145123.43 103474.62 153840.1
## 2036 131289.57 114245.54 148333.59 105222.98 157356.2
## 2037 133921.80 116318.81 151524.80 107000.34 160843.3
## 2038 136554.04 118409.28 154698.79 108804.03 164304.0
## 2039 139186.27 120515.47 157857.07 110631.75 167740.8
## 2040 141818.50 122636.08 161000.92 112481.52 171155.5
## 2041 144450.74 124769.99 164131.48 114351.63 174549.8
## 2042 147082.97 126916.21 167249.74 116240.57 177925.4
## 2043 149715.20 129073.86 170356.55 118147.00 181283.4
## 2044 152347.44 131242.19 173452.69 120069.74 184625.1annotation <- data.frame(
x = c(2020, 2032),
y = c(130000, 100000),
label = c("$108 ± 16 Billion in 2027","$121 ± 21 Billion in 2032")
)
c+ geom_label(data = annotation, aes(x=x, y=y, label=label), size = 3) + labs(title= "Revenue Forecasted using Pre-Covid Data", subtitle = "Own Source and Federal Revenues Combined")
autoplot(tsexp) +
#geom_line(tsexp)+
#geom_line(aes(model_rev))+
autolayer(forecast_rev, series = "Revenue") +
autolayer(forecast_exp, series = "Expenditure)", alpha = 0.5) +
geom_line(year_totals, mapping= aes(x = Year, y = Revenue)) + guides(colour = guide_legend("Forecast")) +
labs(title = "Forecasted Revenue and Expenditures", caption = "Skewed by 2022 federal revenue from Covid response")
Federal Revenue
fed_rev <- ff_rev %>% select(fy, rev_57, rev_58, rev_59) %>%
mutate(fed_total = rev_57+rev_58+rev_59)
fed_ts57 <- ts(fed_rev$rev_57, start ="1998", frequency = 1) # yearly data
model_fed <- auto.arima(fed_ts57, seasonal = FALSE)
forecast_fed <- forecast(model_fed, h = 23)
fed57 <- forecast(forecast_fed, h = 20) %>%
autoplot() +
ylab("Nominal Dollars (Millions)") +
xlab("Year") +
ggtitle("Forecasted Federal Other Revenue") +
theme_classic() +
scale_y_continuous(labels = dollar )
fed57
fed_ts58 <- ts(fed_rev$rev_58, start ="1998", frequency = 1) # yearly data
model_fed <- auto.arima(fed_ts58, seasonal = FALSE)
forecast_fed <- forecast(model_fed, h = 23)
fed58 <- forecast(forecast_fed, h = 20) %>%
autoplot() +
ylab("Nominal Dollars (Millions)") +
xlab("Year") +
ggtitle("Forecasted Federal Transfers for Transportation") +
theme_classic() +
scale_y_continuous(labels = dollar )
fed58
fed_ts59 <- ts(fed_rev$rev_59, start ="1998", frequency = 1) # yearly data
model_fed <- auto.arima(fed_ts59, seasonal = FALSE)
forecast_fed <- forecast(model_fed, h = 23)
fed59 <- forecast(forecast_fed, h = 20) %>%
autoplot() +
ylab("Nominal Dollars (Millions)") +
xlab("Year") +
ggtitle("Forecasted Federal Medicaid Reimbursements") +
theme_classic() +
scale_y_continuous(labels = dollar )
fed59
fed_tstotal <- ts(fed_rev$fed_total, start ="1998", frequency = 1) # yearly data
model_fed <- auto.arima(fed_tstotal, seasonal = FALSE)
forecast_fed <- forecast(model_fed, h = 23)
fedtotal <- forecast(forecast_fed, h = 20) %>%
autoplot() +
ylab("Nominal Dollars (Millions)") +
xlab("Year") +
ggtitle("Forecasted Federal Revenue", subtitle = "Sum of Transportation, Medicaid, and Other Federal Tranfers") +
theme_classic() +
scale_y_continuous(labels = dollar )
fedtotal
fed_tstotal <- ts(fed_rev$fed_total, start ="1998", end = "2019", frequency = 1) # yearly data
model_fed <- auto.arima(fed_tstotal, seasonal = FALSE)
forecast_fed <- forecast(model_fed, h = 23)
fedtotal2 <- forecast(forecast_fed, h = 20) %>%
autoplot() +
ylab("Nominal Dollars (Millions)") +
xlab("Year") +
ggtitle("Forecasted Federal Revenue -- pre-COVID trends", subtitle = "Sum of Transportation, Medicaid, and Other Federal Tranfers") +
theme_classic() +
scale_y_continuous(labels = dollar )
fedtotal2